Excel Magic Trick 881: Lookup Picture with INDEX & MATCH functions instead of INDIRECT Function

No video

Excel Magic Trick 881: Lookup Picture with INDEX & MATCH functions instead of INDIRECT Function

Рет қаралды 238,326

Күн бұрын

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

Пікірлер: 164

@excelisfun 12 жыл бұрын

@ExcelIsFun , try this video: Excel Magic Trick 882: Excel 2007 Bitmap Image Lookup Picture INDEX & MATCH functions

@excelisfun 12 жыл бұрын

@ExcelIsFun , try: Excel Magic Trick 882: Excel 2007 Bitmap Image Lookup Picture INDEX & MATCH functions

@excelisfun 12 жыл бұрын

@TheToiletblock , see this video: Excel Basics #8: Cell References Relative & Absolute or Excel Cell References 15 Examples Formulas, Conditional Formatting & Data Validation

@excelisfun 12 жыл бұрын

I am glad that you liked it!

@wills7166 4 жыл бұрын

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.

@JeffTRepublic 3 жыл бұрын

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!

@tarun_vaish 8 жыл бұрын

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)

@johnco25 7 жыл бұрын

will try this one out. yup i did have that issue thanks! :)

@excelisfun 12 жыл бұрын

@Simonspad1 , THANKS!!! I added a annotation to the video at 3:09 to help others!

@excelisfun 12 жыл бұрын

@Nanpa0 , I am glad that you like it!

@alexandrechabrie2538 7 жыл бұрын

i have spent 3 hours !!!! now it works!!!!! Thhhhannnkkkk you !!!!!!!

@vladimirdomin5877 7 жыл бұрын

Hi, Can you please let me know how you made it work in excel 2013? Thanks

@excelisfun 12 жыл бұрын

@cyrilbrossard , THANKS!!! I added a annotation to the video at 3:09 to help others!

@jurasichero 5 жыл бұрын

i wasted hours on figuring this out and your tutorial helped me Nail it in the first try, Nice work and thanks

@excelisfun 12 жыл бұрын

@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?

@bigdockjingle 4 жыл бұрын

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!

@letsgo780 4 жыл бұрын

I don't get what you said.

@bigdockjingle 4 жыл бұрын

@@letsgo780 did u had the issue "reference is not valid"? otherwise just ignore it

@letsgo780 4 жыл бұрын

@@bigdockjingle I heard reference is not valid but I didnt get ur explanation...;;

@bigdockjingle 4 жыл бұрын

@@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.

@letsgo780 4 жыл бұрын

@@bigdockjingle Thank you for your reply. However, would it be possible for u to record it and post on ur channel?

@chasehandley453 10 жыл бұрын

For those getting "reference not valid", Name the ranges in your index/Match function.

@lb4462la 10 жыл бұрын

what does name your index/Match function mean?

@jacobmaples3251 9 жыл бұрын

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

@hongqirong9927 2 жыл бұрын

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.

@excelisfun 12 жыл бұрын

@Joethemaltaman , I do not. I wonder if anyone else had any ideas?

@user-gl2fq2lf6m 3 жыл бұрын

i love you man !!!!

3 жыл бұрын

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

@hongqirong9927 2 жыл бұрын

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.

@pourquoipa5 12 жыл бұрын

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.

@excelisfun 12 жыл бұрын

Cool! I am glad that it helped!

@excelisfun 12 жыл бұрын

@krn14242 , you are welcome!

@excelisfun 12 жыл бұрын

@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!!

@excelisfun 12 жыл бұрын

@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???

@yasirbakshi005 4 жыл бұрын

Bestest n Easy..! ty u hv just nailed it

@SyedMuzammilMahasanShahi 6 жыл бұрын

That is amazing. I was looking for this, saw some videos by others, but u made it so easy. EXCELlent Mike

@CyrilBrossard 12 жыл бұрын

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.

@roderickdeleeuw2101 7 жыл бұрын

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!

@Nicaner 6 жыл бұрын

Thanks! This solved the issue! You got to lock the cells for the "reference not valid" to dissappear!

@bigdockjingle 4 жыл бұрын

THANK YOU MAN! u really saved me!

@Simonspad1 12 жыл бұрын

@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?

@adamchambers1393 11 жыл бұрын

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?

@Nanpa0 12 жыл бұрын

Cool ... thanks.. always wondered how to do picture in excel.

@ismailismaili0071 6 жыл бұрын

awesome i never knew that we can look up a pic in excel.

@jennysparrow4329 5 жыл бұрын

Excellent lesson !!! Thank you

@reighbernal188 3 жыл бұрын

Really fun and wonderful.. thanks

@ortmll 8 жыл бұрын

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/

@hengleanchanel4403 3 жыл бұрын

Am too still like this Reference Not Valid

@hongqirong9927 2 жыл бұрын

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.

@excelisfun 12 жыл бұрын

@premiumproductions69, From a post at the Mr Excel Message Board, someone said that it does not work in Excel 2007.

@excelisfun 12 жыл бұрын

@rogerimee , I do not know. I also tried VLOOKUP and it did not work.

@deenmohamed6893 9 жыл бұрын

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

@excelisfun 12 жыл бұрын

@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.

@jorohawya2953 6 жыл бұрын

continuously showing "Reference not valid"

@hongqirong9927 2 жыл бұрын

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.

@Kvasir_YYC 8 жыл бұрын

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. :)

@premiumproductions69 12 жыл бұрын

@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 :)

@hughmendoza1 12 жыл бұрын

haha!!! Finally, i found a simple way to do it... Thanks Mike!!!

@MajorAuditor 12 жыл бұрын

Great Lesson

@augustodemelo915 Жыл бұрын

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?

@krn14242 12 жыл бұрын

Thanks Mike.

@Joethemaltaman 12 жыл бұрын

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?

@Simonspad1 12 жыл бұрын

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.

@jacobmaples3251 9 жыл бұрын

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?

@aridewostonework987 3 жыл бұрын

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?

@apparelinnovationclub5899 2 ай бұрын

Tks❤

@cowman447 7 жыл бұрын

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?

@hongqirong9927 2 жыл бұрын

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.

@MohAboAbdo 5 жыл бұрын

Thanks ... Thanks ... Thanks

@jheimsjhuliard1220 5 жыл бұрын

Thx excellsfun😀

@Dimatingqui1174 7 жыл бұрын

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.

@wandadc1900 3 жыл бұрын

Tanks sir

@sayedmohamd5223 8 жыл бұрын

thank you - smart.

@mikkelrasmussen5539 10 жыл бұрын

@Mrampersad- Thanks, you just saved me from destroying my computer, and probably my exam as well

@yudhaswarna 10 жыл бұрын

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

@elsherifaluminum2865 7 жыл бұрын

me too did you find any solution

@sandie1712 7 жыл бұрын

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....

@hongqirong9927 2 жыл бұрын

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.

@yohan9999 10 жыл бұрын

awesome!!!

@dustincogley806 6 жыл бұрын

Thanks!!!

@rafaelrodriguessantos6379 4 жыл бұрын

That's legit!

@rafaelrodriguessantos6379 4 жыл бұрын

Hey there, nice video! Is there any way of importing pictures automatically? Because.... doing that manually it's pretty lame annoying. tks

@karenwridt3518 4 жыл бұрын

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.

@maniZARA 7 жыл бұрын

thanks for useful video! :D

@dzarur 7 жыл бұрын

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

@cmlucifer 10 жыл бұрын

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?

@affindi77 4 жыл бұрын

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?

@salamamohammad7276 4 жыл бұрын

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

@joewills 11 жыл бұрын

I struggled with that. My work around is to select the image - then picture tools, then picture styles and select soft edge rectangles.

@hafidztry 12 жыл бұрын

@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.

@CyrilBrossard 12 жыл бұрын

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?

@1shortv 5 жыл бұрын

Wow!

@555VEL 6 жыл бұрын

It is possible for one place within one cell. But.. Is it possible for image table depending upon cell vale in so many rows....

@zargosar 11 жыл бұрын

VLOOKUP function changes by consultav this is for excel 2010

@rathnakvithana6947 5 жыл бұрын

tks

@ricolalli7249 10 жыл бұрын

getting "Reference not valid"

@elsherifaluminum2865 7 жыл бұрын

me too did you find any solution

@trinboonngam9445 6 жыл бұрын

me too

@555VEL 6 жыл бұрын

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"

@cdsheffer001 6 жыл бұрын

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.

@oboealto 4 жыл бұрын

@@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!

@kenneththam3853 10 жыл бұрын

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".

@maadoratriciazabat2017 7 жыл бұрын

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!

@trinboonngam9445 6 жыл бұрын

what your excel version? exel 2010 is work?

@andrewmoss6449 12 жыл бұрын

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.

@rogerimee 12 жыл бұрын

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

@rmered67 12 жыл бұрын

Same problem as Joel. Excel won't let me out of Picture Tools once I click on the image, meaning I can't enter anything into the Formula bar. (=image name). Any ideas?

@rautorama 11 жыл бұрын

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

@marceloribeirosimoes8959 5 жыл бұрын

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...

@somethingbritish69 9 жыл бұрын

how do you get rid of the border around the image?

@gretchelgretchel1267 5 жыл бұрын

fill up with white color the reference cells

@gabrielasedlackova6907 8 жыл бұрын

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?

@cdsheffer001 6 жыл бұрын

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.

@hongqirong9927 2 жыл бұрын

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.

@jemykirti 4 жыл бұрын

Everything works great, only the picture get cropped, how to fix it?

@hongqirong9927 2 жыл бұрын

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.

@hrach03 11 жыл бұрын

Sorry, forgot to mention, I am playing with the pictures

@mattgerhard302 9 жыл бұрын

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?

@farhanislam3707 5 жыл бұрын

HOW IMAGE IS INSERT FROM FOLDER TO USERFORM AND THEN CELL. IN STUDENT RECORD

@zeynelabidinaydn2818 3 жыл бұрын

Is there a solution for mac?

@aaroncabarle1818 7 жыл бұрын

Its not letting me type in the formula bar after pasting the image.

@Miniononthelean 12 жыл бұрын

what does absolute and relative cell mean please giv example

@neruad1977gmailcom 11 жыл бұрын

hi all any ideas how to make the image to disappear once F1 is empty

@pinoyapollo9637 Жыл бұрын

Hi there why when i do the equal function on the pic it says reference isn't valid 😔 what should I do 🙏🙏🙏

@pinoyapollo9637 Жыл бұрын

Actually i did it already, one more question can i have an error message or error function right in the name manager?

@JohnPauljackulinbaby 11 жыл бұрын

after CTRL+F3 , I insert the formula , then i could not use the formula with selecting pitcher.

@hongqirong9927 2 жыл бұрын

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.

@mostafaengineer3199 3 жыл бұрын

=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

@hongqirong9927 2 жыл бұрын

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.