Excel Magic Trick 881: Lookup Picture with INDEX & MATCH functions instead of INDIRECT Function
HTML-код
- Опубликовано: 13 окт 2024
- Download Excel Start File: people.highlin...
Download workbook: people.highline...
See how to do a lookup for Pictures in Excel using:
Data Validation Dropdown List
INDEX and MATCH Functions
Defined Names
Also see this video:
Mr Excel & excelisfun Trick 42: Lookup Picture In Excel Formula or VBA?:
Excel Magic Trick 637: Lookup A Chart -- Defined Names, INDIRECT function and Formulas In Pictures
Hint for Excel 2007: Excel Magic Trick 882: Excel 2007 Bitmap Image Lookup Picture INDEX & MATCH functions
Hint 2007: Use Bitmap Image instead of picture
I am glad that you liked it!
OUT OF ALL OF THE VIDEOS I WATCHED YOURS WAS THE ONLY ONE THAT WORKED FOR MY APPLICATION. THANK YOU SO MUCH FOR ALL YOU DO.
@Nanpa0 , I am glad that you like it!
God..Thank you! ExcellsFun this is a relief cause I'm trying to build a simple database which includes an automatic insertion of the teacher's e-signature and this really helped. I had learned your index & match "powerful" formula before and I didn't knew I could use this in pictures. Your such a life saver!
@Simonspad1 , THANKS!!! I added a annotation to the video at 3:09 to help others!
@cyrilbrossard , THANKS!!! I added a annotation to the video at 3:09 to help others!
i wasted hours on figuring this out and your tutorial helped me Nail it in the first try, Nice work and thanks
Hi. For some reason in MS365, the reference is the problem, something like the new name, points to nothing, so it can't be used as a reference, as if if had #ref errors or something. I couldn't find a real solution, but what I did is I downloaded your file. I created the name in my file with the same name as referred in your picture, then I copied your picture into my file and allowed it to reutilize the name... This basically the picture "holds" the referecence while copied from one file to the other... and that made the trick. I couldn't have done it without your file, so double thanks :D
HI, maybe this could explain the issue though you had used the Mr. Mike's workbook to address the problem.
Do use the $ sign or press F4 to lock the cells as you use the index match function within the name manager. This will solve the reference isnt valid problem, $D$3 etc...
This case, You got to paste the formula within the name created with the name manager. follow from 3:00 mins onwards. The formula is =INDEX($C$3:$C$5,MATCH($F$1,$A$3:$A$5,0)) as per the worksheet and paste it into the name manager with the newly created word. In this case is BoomPic.
Do not use the arrow keys when you wanna to scroll to the right or back to the left in the formula box. That will cause the creation of the cell value, which is the cell that was highlighted just before pressing Ctrl and F3 together to get to the name manager. Instead, use mouse to pull to the right or the left to check the values if it is exactly the index match function and to make amendments as well. also must lock cells with $ sign as name manager will for some reason have a different cell than what is in the formula.
Additionally, Also specify exact cell (i.e $C$3:$C$5...) if you have created tables instead which will point to the table when the whole range of cells is selected. Although that shouldnt be the problem.
The index function points to where the pictures or answers lies to be taken out with reference to the row, column or area. FOr our case is the row number. The match is to link the value we wish to display from the data validation with the answers that is now lay into array to give the "row number", the "row number" points to the picture that match what we are looking for.
Example, Carlota is row 2, so the answer that was index will look into the second row of the reference cell where the answer lies.
i have spent 3 hours !!!! now it works!!!!! Thhhhannnkkkk you !!!!!!!
Hi,
Can you please let me know how you made it work in excel 2013?
Thanks
Great stuff! This is really helpful. I have a question - If the reference images (on the left) each have a hyperlink linking to a different URL, how can we have that hyperlink property carried over to the dynamic image on the right? This is assuming that VBA and macros can't be used. Thanks in advance.
@krn14242 , you are welcome!
@TheToiletblock , see this video:
Excel Basics #8: Cell References Relative & Absolute
or
Excel Cell References 15 Examples Formulas, Conditional Formatting & Data Validation
Cool! I am glad that it helped!
Bestest n Easy..! ty u hv just nailed it
That is amazing. I was looking for this, saw some videos by others, but u made it so easy. EXCELlent Mike
Cool ... thanks.. always wondered how to do picture in excel.
Excellent lesson !!! Thank you
Whoever is facing problem with the error "Invalid reference", do the following (Worked in Excel 2013 as well):
1. Copy the image.
2. Don't use Ctrl+V to paste the image. Instead, go to the Home->Paste->Paste Special.
3. From the table that pops up, select 'Microsoft Office Drawing Object'.
It should work. To make the image less ugly, follow what Joe Williams has said.
(Pricture Tools->Picture Effects-> Soft Edges, and then play around)
will try this one out. yup i did have that issue thanks! :)
Really fun and wonderful.. thanks
I have been using this trick for a year now... Very helpful... Thanks a lot
I want the picture turn back to blank picture when I delete the "code" cell... It just does not do that now... Can you please help?
Thank you for the video.
Followed instructions all fine and got to naming the picture which is named =BalconyMember
However when I finish the command it states Reference is not valid! Am unable to determine why though.
Any help on this?
@cyrilbrossard , From a post at the Mr Excel Message Board, someone said that it does not work in Excel 2007. I have tried many fixes and have exhausted my resources for what the problem is. Does anyone else know?
awesome i never knew that we can look up a pic in excel.
In excel2013, this is applicable only if the referrence is in the same worksheet. Is there anyway to do it otherwise i.e. the lookup array is in different worksheet? Thanks.
Hey there, nice video! Is there any way of importing pictures automatically? Because.... doing that manually it's pretty lame annoying. tks
Thank you for your excellent explanation. Sometimes, I don't know why, it does not work and shows the error message: "Reference Isn't valid."
Why it happens?
How to solve it?
i love you man !!!!
@jane4sahara , I do not know how to do that. I have tried something link =HYPERLNIK(VLOOKUP)) before, but I can never get it to work.
@Joethemaltaman , I do not. I wonder if anyone else had any ideas?
@premiumproductions69, From a post at the Mr Excel Message Board, someone said that it does not work in Excel 2007.
@ExcelIsFun
I bet it has something to do with which version you are running.
I am using 2007 and have the same problem.
p.s. love your work :)
Hello, any way to make a drop down list that the list is build from the pictures and not from words?
Thank you great guides
@ExcelIsFun , try:
Excel Magic Trick 882: Excel 2007 Bitmap Image Lookup Picture INDEX & MATCH functions
This is great! Is there a way to then link those cells as data label in a chart? When I use the cells with the linked pictures as 'Value from Cell' range, I get blank data labels.
@rmered67 , I do not know. I am not having that problem and when I try to duplicate it, I cannot. Can you send your workbook to excelisfun at gmail? I am very curious what is going on!!
@Simonspad1, From a post at the Mr Excel Message Board, someone said that it does not work in Excel 2007. I have tried many fixes and have exhausted my resources for what the problem is. Does anyone else know?
I have no idea what is causing your problem. Anyone else know???
Dear Sir, I tried this vlookup funtion for picture it was fine until i reach the last tep. Finaly when i select the picture and going to formula bar iam unable write anything in the formula bar while selecting the picture.
Kindly advice how to write the formula while selecting pictures
@rogerimee , I do not know. I also tried VLOOKUP and it did not work.
Hi ExellsFun, I am using v2011 (MAC) and was eventually able to make it work.
As mentioned my concern wasn't to type the defined formula (BoomPic) in the formula bar, that worked fine but the picture wouldn't update.
The picture shouldn't be copied as seen on your video, but the entire cell containing your picture (it seems that Simonspad1 got it too!).
Copy a cell with a pic, paste where you want it to appear, click on the pic, and type into the formula bar "=WhateverNameYouChoose".
Cheers.
I believe I found the answer to the issue of 'reference not valid'. After a lot of frustration I noticed that when I tried to connect the image to the 'name' (=....) the formula in the name manager had changed.
> So use the $$ signs or press F4 on the cells in the formula you upload there and I was good to go.
Good to know:
- Naming my ranges broke my formula, so I switched back to 'hard values'
- Pasting in various ways (paste special) didn't make any difference after I fixed the before mentioned
Hope this helps!
Thanks! This solved the issue! You got to lock the cells for the "reference not valid" to dissappear!
THANK YOU MAN! u really saved me!
It works for me, thanks very much.
However, I try to replace the array method in INDEX & MATCH, instead using A3:A5, I'm using table[Product] method and it does not work. Is there any work around?
Great Lesson
Thanks a lot for this. Finally got it to work in Excel 2007. Still can't get rid of the border around the dynamic image but at least I'm not pulling my hair out now. :)
Me too guys - can't click on the formula bar when the image is selected. Most annoying! Any ideas? PS Best ever Excel tips - keep up the great work.
Hi, first, I want to congratulate you and tell you that you are my best Excel friend. I am using windows 7 but cannot click the formula bar with an image selected. Any idea why?
haha!!!
Finally, i found a simple way to do it...
Thanks Mike!!!
@ExcelIsFun , try this video:
Excel Magic Trick 882: Excel 2007 Bitmap Image Lookup Picture INDEX & MATCH functions
Good trick, but if we have a table of references displaying in multi row photos or shapes with single unique code for each, can we apply the same approach to get tabulated data not only single lookup at a time. e.g Table with column header: CODE {A1,A2A3,A4,A5, ... etc } & Column with photos /shapes for each code ... How can we apply the same formula in multi line within excel to create a data table ? Thanks
Thanks Mike.
great idea, wish it would work with my file... I have a similar file with thousands of pics. named range is fine, but pic wouldn't change according to validation. any idea?
@ExcelIsFun I eventually got it to work as follows: ensure the pictires are within a single cell each, then select one cell (not just the picture). Then copy the cell (with a picture in) and paste as a picture link. This then gives you the formula bar in which you can then post the formula. It seems to work fine and I can email my spreadsheet to you if you like?
Hi, Im new to this. Thank you for this great info!
However, does it works if I just use vlookup formula?
I tried exactly the same method but only change the formula to the "=if(.... vlookup)", in the define name and named it, says TypeC. However whenever I want to put name =TypeC in the formula bar of the pic, it says "Reference is not Valid".
If you are watching this and have the Issue
"'reference not valid' here bellow is the comment that solve this for me(it's kinda lost in the middle of this all junk comments
Roderick de Leeuw
2 anos atrás (editado)
I believe I found the answer to the issue of 'reference not valid'. After a lot of frustration I noticed that when I tried to connect the image to the 'name' (=....) the formula in the name manager had changed.
> So use the $$ signs or press F4 on the cells in the formula you upload there and I was good to go.
Good to know:
- Naming my ranges broke my formula, so I switched back to 'hard values'
- Pasting in various ways (paste special) didn't make any difference after I fixed the before mentioned
Hope this helps!
I don't get what you said.
@@letsgo780 did u had the issue "reference is not valid"? otherwise just ignore it
@@bigdockjingle I heard reference is not valid but I didnt get ur explanation...;;
@@letsgo780 OH! sorry! actually when u type index formula inside the cell name. i was not noticing that excell was not fiting the $$ on the formula like
EXample: =index(4C,5B) i had to press F4 for lock the cells ranges choices in the formulas
Ex: =index($4C$,$5B$) for me it worked when i've appliend the lock with in the function itself.
Sry not native ENG, but i hope i caould help in anyway, if yes, leave it a like so the commment can be in the top.
@@bigdockjingle Thank you for your reply. However, would it be possible for u to record it and post on ur channel?
Hi ... Thank you for sharing and this just what I've been looking for. How ever, many times I've tried the same in excel with the new file then every time when I just want to put the formula into the picture and double-click it or press enter then excel always returns with the message "Reference is not valid".
This is so frustrating me. When yours can be done like just it is shown here while mine when I was trying it with the new file this trick failed.
Can you help me with this problem.
I'm with excel 2010
Thanks
me too
did you find any solution
i have a solution to this problem-- Simply not to pic bitmap image,instead you copy paste any image from the sheet and apply logic on that it works even in 2016...Hope its helpful for you....
Hi, do use the $ sign or press F4 to lock the cells as you use the index match function within the name manager. This will solve the reference isnt valid problem, $D$3 etc...
This case, You got to paste the formula within the name created with the name manager. follow from 3:00 mins onwards. The formula is =INDEX($C$3:$C$5,MATCH($F$1,$A$3:$A$5,0)) as per the worksheet and paste it into the name manager with the newly created word. In this case is BoomPic.
Do not use the arrow keys when you wanna to scroll to the right or back to the left in the formula box. That will cause the creation of the cell value, which is the cell that was highlighted just before pressing Ctrl and F3 together to get to the name manager. Instead, use mouse to pull to the right or the left to check the values if it is exactly the index match function and to make amendments.
Also we must lock the cells with $ sign as name manager will for some reason have a different cell than what is in the formula.
Also specify exact cell (i.e $C$3:$C$5...) if you have created tables instead which will point to the table when the whole range of cells is selected. Although that shouldnt be the problem.
The index function points to where the pictures or answers lies to be taken out with reference to the row, column or area. FOr our case is the row number. The match is to link the value we wish to display from the data validation with the answers that is now lay into array to give the "row number", the "row number" points to the picture that match what we are looking for.
Example, Carlota is row 2, so the answer that was index will look into the second row of the reference cell where the answer lies.
Thx excellsfun😀
Thanks ... Thanks ... Thanks
thanks for useful video! :D
Hi! This video is very helpful I tried it and got lucky enough. However, I encountered a problem. In my drop down list I have 100 list and when I choose say the 7 option from the list it still show the picture however it shows also the columns from the left. SO it doesnt look good. How to fix it. Any advise. Many thanks!
what your excel version? exel 2010 is work?
I am getting my picture to copy into the correct cell. However, it is distorting my image... I have tried changing all of the properties original image and the image being copied too but it is still distorting the image no matter what the image properties are... Any suggestions?
thank you - smart.
awesome!!!
HI Mike,
I would like to ask why the vlookup is not working when you put in define names unlike the Index match and the offset match in define name
Thanks
It is possible for one place within one cell. But..
Is it possible for image table depending upon cell vale in so many rows....
I'm getting an issue where the picture is squished when I select an item from my data validation. everything is responding well, it's just distorting the picture. Any suggestions?
Hi, perhaps you can crop the picture and make sure the picture fits quite neatly without too much gap at the corners of the picture cells to be referenced. It works like a mirror.
Nice! But I want to have trademark names in one column and it's logos aside them.
Could you help me to do that?
...it seems the video author don't answer...
That's legit!
always tells me "Reference Not valid" I've tried to make this work in a number of different ways. Maybe it simply wont work in 2013? Very frustrating. This looks like such a cool trick :o/
Am too still like this Reference Not Valid
HI, do use the $ sign or press F4 to lock the cells as you use the index match function within the name manager. This will solve the reference isnt valid problem, $D$3 etc...
This case, You got to paste the formula within the name created with the name manager. follow from 3:00 mins onwards. The formula is =INDEX($C$3:$C$5,MATCH($F$1,$A$3:$A$5,0)) as per the worksheet and paste it into the name manager with the newly created word. In this case is BoomPic.
Do not use the arrow keys when you wanna to scroll to the right or back to the left in the formula box. That will cause the creation of the cell value, which is the cell that was highlighted just before pressing Ctrl and F3 together to get to the name manager. Instead, use mouse to pull to the right or the left to check the values if it is exactly the index match function and to make amendments.
Also we must lock the cells with $ sign as name manager will for some reason have a different cell than what is in the formula.
Also specify exact cell (i.e $C$3:$C$5...) if you have created tables instead which will point to the table when the whole range of cells is selected. Although that shouldnt be the problem.
The index function points to where the pictures or answers lies to be taken out with reference to the row, column or area. FOr our case is the row number. The match is to link the value we wish to display from the data validation with the answers that is now lay into array to give the "row number", the "row number" points to the picture that match what we are looking for.
Example, Carlota is row 2, so the answer that was index will look into the second row of the reference cell where the answer lies.
For those getting "reference not valid", Name the ranges in your index/Match function.
what does name your index/Match function mean?
Sal Ortiz Instead of selecting cells for your index match function just type the name of the named range. (Example of the named range in the video is cells A3:A5. Hope this helps
HI, or you can try this.
do use the $ sign or press F4 to lock the cells as you use the index match function within the name manager. This will solve the reference isnt valid problem, $D$3 etc...
This case, You got to paste the formula within the name created with the name manager. follow from 3:00 mins onwards. The formula is =INDEX($C$3:$C$5,MATCH($F$1,$A$3:$A$5,0)) as per the worksheet and paste it into the name manager with the newly created word. In this case is BoomPic.
Do not use the arrow keys when you wanna to scroll to the right or back to the left in the formula box. That will cause the creation of the cell value, which is the cell that was highlighted just before pressing Ctrl and F3 together to get to the name manager. Instead, use mouse to pull to the right or the left to check the values if it is exactly the index match function and to make amendments.
Also we must lock the cells with $ sign as name manager will for some reason have a different cell than what is in the formula.
Also specify exact cell (i.e $C$3:$C$5...) if you have created tables instead which will point to the table when the whole range of cells is selected. Although that shouldnt be the problem.
The index function points to where the pictures or answers lies to be taken out with reference to the row, column or area. FOr our case is the row number. The match is to link the value we wish to display from the data validation with the answers that is now lay into array to give the "row number", the "row number" points to the picture that match what we are looking for.
Example, Carlota is row 2, so the answer that was index will look into the second row of the reference cell where the answer lies.
@Mrampersad- Thanks, you just saved me from destroying my computer, and probably my exam as well
How do I get rid of the border that surrounds the image? I know if I get rid of the border lines from the table it gets rid of them on the copied image, but is there a more conventional method? I've tried 'Picture Styles', but I can't get one which is totally blank so only the image is seen. Thanks in advance.
works perfectly, but is there any way how to hide/unhide pictures woth this formula programatically by VBA? These picture are not envolved in worksheets.pictures collection as well as in .shapes collection. Can anybody help me?
Tanks sir
Tks❤
I've been able to simulate this perfectly in Excel 2010 but since I crossed over to Excel 2013 I'm no longer able to get the image part to work. Anybody have a clue how to make the images work with 2013?
Using Excel 2013 64 Bit. - I tried doing the steps in your video and could not get it to work. so I just downloaded your excel file. your file also does not work. when I go to the answer tab and change the drop down from Bellen to deuce etc.. the price changes.. the photo just stays the same. have you by chance confirmed functionality on 64 bit? or is there pre configuring to your excel that needs to be done to enable this functionality?
Wow!
Everything works great, only the picture get cropped, how to fix it?
Hi, do fit the picture with little gap around the corners of the cell where the initial picture lies to smooth out the pictures. Take it as if it is a mirror.
@premiumproductions69
Hai mike, I think For version 2007 it can use camera tools bar mike,to paste into picture cells. if it doesnt work, God bless you mike.
I struggled with that. My work around is to select the image - then picture tools, then picture styles and select soft edge rectangles.
tks
Hi there why when i do the equal function on the pic it says reference isn't valid 😔 what should I do 🙏🙏🙏
Actually i did it already, one more question can i have an error message or error function right in the name manager?
The "Reference Not Valid" errors I found my issue was that the formula pasted did not stay the same after saving it. I had to go in and correct it in the Refers to: section. Validate the formula from there to make sure it is correct. It changed my Match section for some reason causing it to error.
Hi i found an explanation to that, perhaps it may help.
Do not use the arrow keys when you wanna to scroll to the right or back to the left in the formula box. That will cause the creation of the cell value, which is the cell that was highlighted just before pressing Ctrl and F3 together to get to the name manager. Instead, use mouse to pull to the right or the left to check the values if it is exactly the index match function and to make amendments as well. also must lock cells with $ sign as name manager will for some reason have a different cell than what is in the formula.
Additionally, Also specify exact cell (i.e $C$3:$C$5...) if you have created tables instead which will point to the table when the whole range of cells is selected. Although that shouldnt be the problem.
The index function points to where the pictures or answers lies to be taken out with reference to the row, column or area. FOr our case is the row number. The match is to link the value we wish to display from the data validation with the answers that is now lay into array to give the "row number", the "row number" points to the picture that match what we are looking for.
Example, Carlota is row 2, so the answer that was index will look into the second row of the reference cell where the answer lies.
Is there a solution for mac?
=INDEX(Table7[Picture],MATCH('Data Entry'!F2,Table7[Model No.],0))
hello! I followed all your steps, if I write something in picture table cells the value shows correctly in targeted cell but when I make the name range and type it in my copied picture as you said excel keeps showing me reference isn't valid
I'm using excel 2019
any help please
HI, do use the $ sign or press F4 to lock the cells as you use the index match function within the name manager. This will solve the reference isnt valid problem, $D$3 etc...
This case, You got to paste the formula within the name created with the name manager. follow from 3:00 mins onwards. The formula is =INDEX($C$3:$C$5,MATCH($F$1,$A$3:$A$5,0)) as per the worksheet and paste it into the name manager with the newly created word. In this case is BoomPic.
Do not use the arrow keys when you wanna to scroll to the right or back to the left in the formula box. That will cause the creation of the cell value, which is the cell that was highlighted just before pressing Ctrl and F3 together to get to the name manager. Instead, use mouse to pull to the right or the left to check the values if it is exactly the index match function and to make amendments.
Also we must lock the cells with $ sign as name manager will for some reason have a different cell than what is in the formula.
Also specify exact cell (i.e $C$3:$C$5...) if the table7 classification you have is not working. Although that shouldnt be the problem.
The index function points to the where the pictures or answers lies to be taken out with reference to the row, column or area. FOr our case is the row number. The match is to link the value we wish to display from the data validation with the answers that is now lay into array to give the "row number", the "row number" points to the picture that match what we are looking for.
Example, Carlota is row 2, so the answer that was index will look into the second row of the reference cell where the answer lies.
how do you get rid of the border around the image?
fill up with white color the reference cells
continuously showing "Reference not valid"
HI, do use the $ sign or press F4 to lock the cells as you use the index match function within the name manager. This will solve the reference isnt valid problem, $D$3 etc...
This case, You got to paste the formula within the name created with the name manager. follow from 3:00 mins onwards. The formula is =INDEX($C$3:$C$5,MATCH($F$1,$A$3:$A$5,0)) as per the worksheet and paste it into the name manager with the newly created word. In this case is BoomPic.
Do not use the arrow keys when you wanna to scroll to the right or back to the left in the formula box. That will cause the creation of the cell value, which is the cell that was highlighted just before pressing Ctrl and F3 together to get to the name manager. Instead, use mouse to pull to the right or the left to check the values if it is exactly the index match function and to make amendments as well. also must lock cells with $ sign as name manager will for some reason have a different cell than what is in the formula.
Additionally, Also specify exact cell (i.e $C$3:$C$5...) if you have created tables instead which will point to the table when the whole range of cells is selected. Although that shouldnt be the problem.
The index function points to where the pictures or answers lies to be taken out with reference to the row, column or area. FOr our case is the row number. The match is to link the value we wish to display from the data validation with the answers that is now lay into array to give the "row number", the "row number" points to the picture that match what we are looking for.
Example, Carlota is row 2, so the answer that was index will look into the second row of the reference cell where the answer lies.
HOW IMAGE IS INSERT FROM FOLDER TO USERFORM AND THEN CELL.
IN STUDENT RECORD
Please help , in the last step when u write =boompic and enter , the excel gave me an Error ( reference not valid ) , please help me
Have the same issue=(
HI, do use the $ sign or press F4 to lock the cells as you use the index match function within the name manager. This will solve the reference isnt valid problem, $D$3 etc...
This case, You got to paste the formula within the name created with the name manager. follow from 3:00 mins onwards. The formula is =INDEX($C$3:$C$5,MATCH($F$1,$A$3:$A$5,0)) as per the worksheet and paste it into the name manager with the newly created word. In this case is BoomPic.
Do not use the arrow keys when you wanna to scroll to the right or back to the left in the formula box. That will cause the creation of the cell value, which is the cell that was highlighted just before pressing Ctrl and F3 together to get to the name manager. Instead, use mouse to pull to the right or the left to check the values if it is exactly the index match function and to make amendments as well. also must lock cells with $ sign as name manager will for some reason have a different cell than what is in the formula.
Additionally, Also specify exact cell (i.e $C$3:$C$5...) if you have created tables instead which will point to the table when the whole range of cells is selected. Although that shouldnt be the problem.
The index function points to where the pictures or answers lies to be taken out with reference to the row, column or area. FOr our case is the row number. The match is to link the value we wish to display from the data validation with the answers that is now lay into array to give the "row number", the "row number" points to the picture that match what we are looking for.
Example, Carlota is row 2, so the answer that was index will look into the second row of the reference cell where the answer lies.
what does absolute and relative cell mean please giv example
VLOOKUP function changes by consultav this is for excel 2010
after CTRL+F3 , I insert the formula , then i could not use the formula with selecting pitcher.
HI, do use the $ sign or press F4 to lock the cells as you use the index match function within the name manager. This will solve the reference isnt valid problem, $D$3 etc...
This case, You got to paste the formula within the name created with the name manager. follow from 3:00 mins onwards. The formula is =INDEX($C$3:$C$5,MATCH($F$1,$A$3:$A$5,0)) as per the worksheet and paste it into the name manager with the newly created word. In this case is BoomPic.
Do not use the arrow keys when you wanna to scroll to the right or back to the left in the formula box. That will cause the creation of the cell value, which is the cell that was highlighted just before pressing Ctrl and F3 together to get to the name manager. Instead, use mouse to pull to the right or the left to check the values if it is exactly the index match function and to make amendments as well. also must lock cells with $ sign as name manager will for some reason have a different cell than what is in the formula.
Additionally, Also specify exact cell (i.e $C$3:$C$5...) if you have created tables instead which will point to the table when the whole range of cells is selected. Although that shouldnt be the problem.
The index function points to where the pictures or answers lies to be taken out with reference to the row, column or area. FOr our case is the row number. The match is to link the value we wish to display from the data validation with the answers that is now lay into array to give the "row number", the "row number" points to the picture that match what we are looking for.
Example, Carlota is row 2, so the answer that was index will look into the second row of the reference cell where the answer lies.
hi all
any ideas how to make the image to disappear once F1 is empty
Its not letting me type in the formula bar after pasting the image.
Hi sir, why mine becomes reference is not valid, im using excel 2010
When i go to formula bar and do the equal function it says reference is not valid 😔
I did successfully use the index &match formula. But then, for an example I just put any name in the product column, the picture still there.
How to let the picture column be blank as well if i put wrong product name (not in the list)?
Hi, you can reference an extra empty cell and type "nil" in the adjacent product column. That way, it will display nothing. As for wrong product name, you can select more than 3 cells in the product column during the data validation portion. Then, reference the index match function with an extra row without picture in the picture column as well as no words under the product column.
it does not work in Excel 2007, please help for excel 2007
Important thing here is to copy and paste - as a linked picture
When i did a simple copy and paste, this was not working.
By the way i was trying this trick with inserted symbols and not pictures
1. Got "reference isn't valid"
2. downloaded Excel file does not contain any of the formulas shown on this video and is naturally not working
3. Excel may truly copy the formula you copy, but when you need it pasted, it clears off the buffer (this shows on the video too, the guy didn't lose it, Excel lost it for him)
4. So I realized I forgot to include ,0 (for match type) when redoing it, so I fixed it.
5. I thought that would do, but still... "reference isn't valid". Tried that on both an existing and new file.
6. Also note that (it seems as though) you may not use table columns as reference (for example [@Country]). Instead you'd have to use the actual cell reference (e.g. $D$3).
Looking for help here. Thanks guys!
HI, do use the $ sign or press F4 to lock the cells as you use the index match function within the name manager. This will solve the reference isnt valid problem, $D$3 etc...
This case, You got to paste the formula within the name created with the name manager. follow from 3:00 mins onwards. The formula is =INDEX($C$3:$C$5,MATCH($F$1,$A$3:$A$5,0)) as per the worksheet and paste it into the name manager with the newly created word. In this case is BoomPic.
Do not use the arrow keys when you wanna to scroll to the right or back to the left in the formula box. That will cause the creation of the cell value, which is the cell that was highlighted just before pressing Ctrl and F3 together to get to the name manager. Instead, use mouse to pull to the right or the left to check the values if it is exactly the index match function and to make amendments as well. also must lock cells with $ sign as name manager will for some reason have a different cell than what is in the formula.
Additionally, Also specify exact cell (i.e $C$3:$C$5...) if you have created tables instead which will point to the table when the whole range of cells is selected. Although that shouldnt be the problem.
The index function points to where the pictures or answers lies to be taken out with reference to the row, column or area. FOr our case is the row number. The match is to link the value we wish to display from the data validation with the answers that is now lay into array to give the "row number", the "row number" points to the picture that match what we are looking for.
Example, Carlota is row 2, so the answer that was index will look into the second row of the reference cell where the answer lies.
getting "Reference not valid"
me too
did you find any solution
me too
Hai frnds... Here is the solution...
"Match(lookup_value," Make as a constant... Please press F4 or put $ symbol.... Otherwise it will display "reference is not valid"
I did. it is because the formula is actually not correct in the reference usually. I found that excel has a hard time keeping it the same even if you paste it.
@@555VEL tried that too, still not working. Here's my formula:
=INDEX(Settings!$C$3:$C$22,MATCH(Main!$C3,Settings!B$3:B$24,0))
what am I doing wrong?
thanks a lot!
I was do same as you
But cell can see only 0
Why
HI, do use the $ sign or press F4 to lock the cells as you use the index match function within the name manager. This will solve the reference isnt valid problem, $D$3 etc...
This case, You got to paste the formula within the name created with the name manager. follow from 3:00 mins onwards. The formula is =INDEX($C$3:$C$5,MATCH($F$1,$A$3:$A$5,0)) as per the worksheet and paste it into the name manager with the newly created word. In this case is BoomPic.
Do not use the arrow keys when you wanna to scroll to the right or back to the left in the formula box. That will cause the creation of the cell value, which is the cell that was highlighted just before pressing Ctrl and F3 together to get to the name manager. Instead, use mouse to pull to the right or the left to check the values if it is exactly the index match function and to make amendments.
Also we must lock the cells with $ sign as name manager will for some reason have a different cell than what is in the formula.
Also specify exact cell (i.e $C$3:$C$5...) if you have created tables instead which will point to the table when the whole range of cells is selected. Although that shouldnt be the problem.
The index function points to where the pictures or answers lies to be taken out with reference to the row, column or area. FOr our case is the row number. The match is to link the value we wish to display from the data validation with the answers that is now lay into array to give the "row number", the "row number" points to the picture that match what we are looking for.
Example, Carlota is row 2, so the answer that was index will look into the second row of the reference cell where the answer lies.