This worked perfectly for what I needed! Great explanation as well!! One upgrade I had to create was using an INDIRECT function within this formula because the cell I was referencing wasn't one with a drop down menu (It was actually a cell with a formula that was dependent on a few other inputs). Basically, I wanted the final results of the formula (within the cell) as a trigger for the picture I wanted displayed. So...within the formula you have, right after the MATCH function, I have an INDIRECT function for the cell I'm referencing. So instead of "=INDEX($B$3:$C$22,MATCH($E$3,$B$3:$E$22,0),2)" .......I used "=INDEX($B$3:$C$22,MATCH(INDIRECT("$E$3"),$B$3:$E$22,0),2)" This gives the "$E$3" cell the ability to be a variable cell with multiple different values, instead of a "data validation" list, and the quotes around the cell reference only takes the cell value. Posted this in the hopes this may help others. :)
I have been working on this for THREE HOURS. I found your video and bingo - success. I am absolutely in love with you right now, you just cant even imagine.
Hi Sir I did not quite catch your name but I must say a big 'Thank you', for the help on this function. I would never have figured that one out. In the process, I found another person's work which resizes a picture to fit the cell edge to edge while maintaining the aspect ratio of the picture. (Clearly, the cell height & width must have the same aspect ratio of the picture to completely fill the cell if that is one's intent. A macro was supplied in order to perform this). Then when you copy it, Excel recognizes that the picture is connected to the cell. I must thank you very much for this work & the time you spent in sharing it with someone without your capabilities! Thank you sir & hope you enjoy your football season. Brian from Canada
Once I figured it out....Perfect results! THANKS!!! Worked like a charm! (I had to put the data used on the same sheet as the picture. I was trying to pull data from another sheet, and not having any success.)
Sumit, Terrific trick and works like a charm. In my case the Names and Logos were on a separate sheet to my main worksheet within the same workbook and it worked first time. Have duplicated similar "lookups" in other workbooks/worksheets and they all work perfectly. Thanks, keep up the great tips.
Hi Sumit many thanks indeed. I tried to follow another video without success, but your explanations were very clear and I have now got my test version working as per your examples.
Thanks for your help. The pictures came over to the new document as you described, however when I made the new document into a PDF / print fille the pictures were not visible. Can you help?
Thanks a lot. The only challenge I have with picture lookup is removing the background and border without losing the picture quality. I can't seem to get around this.
Amazing! This is exactly what I was looking for. I'm trying to make my own custom conditional formatting icon set with images from the web. This method would work real well when I modify it a bit for my purpose. Thankyou so much! :-)
Hi Great Video just what i needed. I am having an issue though with it returning the image from the cell i lookup. I can return text but no image. Can you please advise? I have basically pasted lots of images into the cells within the cell boundary, but still not returning an image. Thanks in advance for any assistance.
Mate, very well done. Helped me a lot. I am now trying to get the Data from another WORKBOOK. ie: Drop Box refers to another workbook sheet which has the picture. Data will not work to another workbook. Trying a few things but taking time. Don't suppose you already have the answer?
Hello Sumit, this is a great video, easy to follow tutorial. So, first: Congratulations and thank you for your work. Now, what would you do if you wanted to have multiple images lookups in the same page? For instance, imagine that you want to do this week football fixtures, or the previous results. Would you repeat this process for each image ? So you’d have 18 Name ranges with a different formula each one of them? Or how would you do it? Thank you very much in advance for your answer.
Did you ever get a response? I was able to create the image lookup as he showed but that`s really not helpful unless I can have multiple rows with the same function in the dropdown list. Thanks so much!
this sounds good, I was wondering whether I can upload my picture sheet which I have 1 column is the product code, 2nd column is the picture and I have 1000 pictures in this excel sheet. when I put my order in, for example I like to order 100 products with product code, when I put the order code in, it will come out with the picture automatically, can you tell me how to do that, thank you so much.
Dear Sumir, Thanks for the tutorial. Very interesting. Just one challenge,,, I tried it step by step and I could get the same result. I have to submit a dashboard on social media data (queries from FB, Twitter etc,) and therefore I want to use the icons instead of the names. Please help me? I have a table from 2019 Jan to date.... I look forward in hearing from you. Dashboard is due Monday, 10 Feb 2020
Hi Sir, good video. Can i substitute the list lookup to text? e.g. instead of a dropdown list where you select the club names it will display each option in a complete list
Thanks for the tips! My picture links keep appearing cropped even though the source cell has the image at the correct size within the cell. Do you know how to fix this?
Sir, how to lookup four (4) different pictures in 4 different cells from a list of picuters in a column. You explained one picture lookup this is very usefull for me but I want 4 pictures in four different cells, how this is possible ? please reply or make this type of video.
I've spent AGES trying to get this to work. Kept getting "Reference isn't valid" error. So others don't experience this frustration...The whole thing needs to be on the SAME WORKSHEET. If you are referring to named range that is on a DIFFERENT worksheet, this method will not work. Thanks.
Hi Sumit, Nice video, this really helps. I would like to know how to populate multiple rows with different pictures, would you be able to help me with that. regards, vivek
Hi, I had insert pictures into cell & link picture as per video but there isn't any picture show after I insert my name formula. Do you know the root cause?
Thank you ! However when I try to do that between 2 work sheets in the same document it doesn't work! Could You tell me how I can do it ? thank you so much.
@@khilandavda I too was stuck at that. But now it is solved. When you copy the cell of the picture and paste from paste special (Linked picture), before that you will have to select the cell in a sheet as per your choice you want to copy and then paste.
you have to use cell ranges in your formula, if you use table names or cell names, it will not work. Try switching it to actual cell ranges like A11:B22 or whatever your range is.
Do you have to be using a data validation list, or should it work if I just type the name in that I need. For whatever reason after I followed your steps I get 'Reference is not valid'
my formula is absolute, but Im still getting the same error message "reference not valid" =INDICE('Data Base'!$D$3:$E$152,COINCIDIR('Control Page'!$I$3,'Data Base'!$D$3:$D$152,0),2)
***** good after noon Thank you for the message. I've been trying to follow these steps for the past one and half hour, I'm not having any luck. Is it possible to email you the file to look at please? I was able to do it on the same sheet(tab) , but I've been trying to having it showing on the original sheet(tab) what is your email address. Thank you.
![F.HERO Ft. JSPKK x ลำไย ไหทองคำ x M-PEE - ไม่สนิทบิดหมด (Thai Riders Anthem) [Official MV]](http://i.ytimg.com/vi/kPJZ0UihBfk/mqdefault.jpg)
Brilliantly explained, the best I’ve seen on TH-cam. Thank you.
I now have a very useful, compact order form with lookup images! Who knew?!. Thanks for the trick!
This worked perfectly for what I needed! Great explanation as well!! One upgrade I had to create was using an INDIRECT function within this formula because the cell I was referencing wasn't one with a drop down menu (It was actually a cell with a formula that was dependent on a few other inputs). Basically, I wanted the final results of the formula (within the cell) as a trigger for the picture I wanted displayed.
So...within the formula you have, right after the MATCH function, I have an INDIRECT function for the cell I'm referencing. So instead of
"=INDEX($B$3:$C$22,MATCH($E$3,$B$3:$E$22,0),2)" .......I used "=INDEX($B$3:$C$22,MATCH(INDIRECT("$E$3"),$B$3:$E$22,0),2)"
This gives the "$E$3" cell the ability to be a variable cell with multiple different values, instead of a "data validation" list, and the quotes around the cell reference only takes the cell value.
Posted this in the hopes this may help others. :)
Ultimate tutorial on Picture look up in MS Excel. I linked so much. It has cleared all doubts related to picture lookup.
I have been working on this for THREE HOURS. I found your video and bingo - success. I am absolutely in love with you right now, you just cant even imagine.
Hi Sir
I did not quite catch your name but I must say a big 'Thank you', for the help on this function. I would never have figured that one out. In the process, I found another person's work which resizes a picture to fit the cell edge to edge while maintaining the aspect ratio of the picture. (Clearly, the cell height & width must have the same aspect ratio of the picture to completely fill the cell if that is one's intent. A macro was supplied in order to perform this). Then when you copy it, Excel recognizes that the picture is connected to the cell.
I must thank you very much for this work & the time you spent in sharing it with someone without your capabilities!
Thank you sir & hope you enjoy your football season.
Brian from Canada
you are the only one who gave me a tutorial that worked for this function. Thank you!
Hands down the best way to do this. I just spent 2+ hours trying other methods and this was by far the best. THANK YOU!
THANK YOU!!!! This is the BEST and easiest to understand for looking up pictures in excel. YOU ARE GREAT!
Thank you very much sir for the picture lookup video. So simple and easy to understand.
This is amazing! I’m so excited. This is the first useful new excel trick I’ve seen in far too long. Thank you so much!!
Thank you so much! I've seen lots of ways to do this, but yours is the simplest and most elegant. And it works!
Susan Van Iderstine actually the way it is explained is something u never see on TH-cam.. the best so far... very helpful..
Once I figured it out....Perfect results! THANKS!!! Worked like a charm! (I had to put the data used on the same sheet as the picture. I was trying to pull data from another sheet, and not having any success.)
Very useful video. Explained in easily understanding way. Thank you very much
Very simple and useful tutorial. Explained in such an understanding way. Thank you very much
Sumit, Terrific trick and works like a charm. In my case the Names and Logos were on a separate sheet to my main worksheet within the same workbook and it worked first time. Have duplicated similar "lookups" in other workbooks/worksheets and they all work perfectly. Thanks, keep up the great tips.
Hi Sumit many thanks indeed. I tried to follow another video without success, but your explanations were very clear and I have now got my test version working as per your examples.
Thank you for your tutorial, I tried others but yours made it work for me. Well done!
Best Tutotial ever ! Took me two weeks to figure out. Thank you :)
+Brandon Lowman I agree... Made what I thought was impossible, possible.
Thank you very much, very simple and efficient!
Thank you so much for your nice and essential advance presentation
WOW!
Thank you for sharing this trick. Very helpful, indeed!
Thank you very much, this information is exact;y what i needed. Hope for future content on excel!
Your video tutorial awes me! Thanks. :)
செம நண்பா 👍
thank you so oooooo much bro. To me,ur video is like something valuable found among the lot of similar but crap videos.
Thank you so much. This technique is very useful for me.
Thanks for your help. The pictures came over to the new document as you described, however when I made the new document into a PDF / print fille the pictures were not visible. Can you help?
thank you Sumit! This is very helpful.
Well done, thank you! secret here is: copy picture and paste by the way (Home => Paste button => paste link Picture) (i got it)
Thank you! Very good video and helped me achieve what I needed to do. Remember to follow Arsenal. Thanks for the great video!
superb 1... Very useful video.. it has helped me a lot...
THANKS FOR INFORMATION ITS USEFUL FOR ME
You are excellent !
Thank you so much.
Thanks a lot. The only challenge I have with picture lookup is removing the background and border without losing the picture quality. I can't seem to get around this.
This formula is helpful for my task. Thanks.
But I cannot apply it in another sheet. can you recommend one of your videos with the said topic?
I have this same question
You really teach awesome! thx a lot
thank you so much for the tutorial
Niiice! I like the way you tricked the system :)
This has really helped me out.. thank you for sharing!
Amazing! This is exactly what I was looking for. I'm trying to make my own custom conditional formatting icon set with images from the web. This method would work real well when I modify it a bit for my purpose. Thankyou so much! :-)
Thanks Sir... This is very useful..... super..
Hi Great Video just what i needed. I am having an issue though with it returning the image from the cell i lookup. I can return text but no image. Can you please advise? I have basically pasted lots of images into the cells within the cell boundary, but still not returning an image. Thanks in advance for any assistance.
can you do this for multiple rows? Also, what if the validation list is blank or you delete the lookup value? thx
Mate, very well done. Helped me a lot. I am now trying to get the Data from another WORKBOOK. ie: Drop Box refers to another workbook sheet which has the picture. Data will not work to another workbook. Trying a few things but taking time. Don't suppose you already have the answer?
+Brian Forbes Interesting... did you get any further with this?
Im trying something similar but within the same workbook just different sheets. Having a hard time. Got any insights you're willing to share?
@@Sandwichplss I'm trying the same without success! Did you get to any solution?
THANKS A LOT SIR
Thanks sir .how do photo with ID num type in cell.
Hello Sumit, this is a great video, easy to follow tutorial. So, first: Congratulations and thank you for your work.
Now, what would you do if you wanted to have multiple images lookups in the same page? For instance, imagine that you want to do this week football fixtures, or the previous results.
Would you repeat this process for each image ? So you’d have 18 Name ranges with a different formula each one of them? Or how would you do it?
Thank you very much in advance for your answer.
Did you ever get a response? I was able to create the image lookup as he showed but that`s really not helpful unless I can have multiple rows with the same function in the dropdown list. Thanks so much!
I have exactly the same problem... "Reference not valid" an all my references are absolute...
its 3 years late, but it might help to anyone facing the same problems. Cell reference needs to be locked ($) in order for this to work
Select blank cell and create named reference using Name Manger(Ctrl+ F3) with choose formula or Indirect formula as the case may be.
put in $X$X for the fixed column ， because name range only capture exaclty one cell only.
this sounds good, I was wondering whether I can upload my picture sheet which I have 1 column is the product code, 2nd column is the picture and I have 1000 pictures in this excel sheet. when I put my order in, for example I like to order 100 products with product code, when I put the order code in, it will come out with the picture automatically, can you tell me how to do that, thank you so much.
Dear Sumir, Thanks for the tutorial. Very interesting. Just one challenge,,, I tried it step by step and I could get the same result. I have to submit a dashboard on social media data (queries from FB, Twitter etc,) and therefore I want to use the icons instead of the names. Please help me? I have a table from 2019 Jan to date.... I look forward in hearing from you. Dashboard is due Monday, 10 Feb 2020
Hi Sir, good video. Can i substitute the list lookup to text? e.g. instead of a dropdown list where you select the club names it will display each option in a complete list
Hello there,
How do I go about adding more names and pictures? I downloaded your file, and I was able to read part of it. Thank you.
Great, thanks a lot!!!!
Muchas gracias!!!! owesome
th-cam.com/video/NyWGgFPkpko/w-d-xo.html
Thanks for the tips! My picture links keep appearing cropped even though the source cell has the image at the correct size within the cell. Do you know how to fix this?
Sir, how to lookup four (4) different pictures in 4 different cells from a list of picuters in a column.
You explained one picture lookup this is very usefull for me but I want 4 pictures in four different cells, how this is possible ?
please reply or make this type of video.
I've spent AGES trying to get this to work. Kept getting "Reference isn't valid" error. So others don't experience this frustration...The whole thing needs to be on the SAME WORKSHEET. If you are referring to named range that is on a DIFFERENT worksheet, this method will not work. Thanks.
thanks a lot, sir.
Hi Sumit,
Nice video, this really helps.
I would like to know how to populate multiple rows with different pictures, would you be able to help me with that.
regards,
vivek
thank you!
How to make it works if the dataset is in another xlsx file ? Is it possible ?
How can I look for multiple pictures at the same time
Hi, I had insert pictures into cell & link picture as per video but there isn't any picture show after I insert my name formula. Do you know the root cause?
Too many arguments error in formula shows when I entered formula index(cells,match(cell, cells,0,2))
please guide me
Hey can anyone offer any help? Im trying to link my active x buttons to display a certain logo on a dashboard that i am creating.
Hi Sumit, can I paste (link picture) in different sheet
Thank you ! However when I try to do that between 2 work sheets in the same document it doesn't work! Could You tell me how I can do it ? thank you so much.
If you have tables named in your sheets, it can throw everything off; you have to use cell ranges (A1:B22) in your formulas to make it work.
Is there a way to include an "=IF" so instead of returning a 0 a blank would be shown in place of the pixture?
Did you figure this out yet i have been looking for an aswer to this
If I don't use data list I want cell to cell picture without use drop down list how is it possible to capture match picture
Need Help... i completely understood the process however i need to lookup images into new sheet for entire coloum and not the drop down list.
can you please help me ?
@@khilandavda I too was stuck at that. But now it is solved. When you copy the cell of the picture and paste from paste special (Linked picture), before that you will have to select the cell in a sheet as per your choice you want to copy and then paste.
which version of Office u use?
my image is not pasting as picture link, the option was only available for text. Please help
Thanks
Hi Summit , I done same thing for me the error is reference is invalid for this u can make a video how to correct this one it will helpus to correct
getting same error
you have to use cell ranges in your formula, if you use table names or cell names, it will not work. Try switching it to actual cell ranges like A11:B22 or whatever your range is.
Excel Changes my hyperlinks WHY ? How do you make a hyperlink that CAN NOT BY TAMPERED WITH
Do you have to be using a data validation list, or should it work if I just type the name in that I need. For whatever reason after I followed your steps I get 'Reference is not valid'
+Brandon Hartman im also having the same Problem..
I have trouble making it in excel 2011 mac, someone who can help?
I have 120 pictures. Is there a way to do this without creating 120 named ranges?
thank you ....
Brother After Lookup Picture no show in print file but other picture show please help me
شكرا
Do you know why my 'linked picture' looks cropped?
my formula is absolute, but Im still getting the same error message "reference not valid"
=INDICE('Data Base'!$D$3:$E$152,COINCIDIR('Control Page'!$I$3,'Data Base'!$D$3:$D$152,0),2)
hi, can you do this with gif?
I've watched this 100 times and still get an error "This formula is missing a range reference or a defined name" EVERY TIME!!! So frustrated!
Why should Arsenal on top? I can't find this team in EPL standing. Don't know where...
How i insert picture in cell
Plz send video
thanks
The tutorial does not specify which version of Excel this technique will work in. It doesn't work in 2010 so far as I can tell.
it says that the reference is invalid😢😢
Im getting a "Reference not valid" error message
I still get 0 despite all of this. PLEASE HELP!!
Indirect formular
Getting Error Reference is not Valid
hello, may i please ask you to email me the steps in writing. thank you
dan
***** good after noon Thank you for the message. I've been trying to follow these steps for the past one and half hour, I'm not having any luck. Is it possible to email you the file to look at please? I was able to do it on the same sheet(tab) , but I've been trying to having it showing on the original sheet(tab) what is your email address. Thank you.
link below film not find
th-cam.com/video/NyWGgFPkpko/w-d-xo.html
Christ, old excel use to just automatically snap an image and you could resize in cell....
HELLO ME THROWS THE REFERENCE IS INVALID
HELP
JHON ESPAÑA make sure all your cell references are absolute.
Sir, can you talk in hindi ? then I will explain it better.
a