All we know that, Index-Match can returns Text or Numbers. But did you try to return any image with Index-Match formula? I guess No. Yes, Index-Match formula both can return image also. So, want to know how to do it? Ok, let's try.
My Simple Project:
To do this, I would like to design a project first. My project is, for analyzing purpose I need to see the Phone Specification of many Brands and their many models. Assume that, your Management asked for a report where, they will just select a Model Name, and the details will display including image. To make this example realistic, I have used Motorola Mobile Phone Brand and their few models and features, which I've collected from www.gsmarena.com. My raw data sheet format are given as below but it would be better if you gather more data of all Brands and their models with image and BDT Price. Please note that, RawData is the Sheet name where raw data gathered.
Report Format:
After gathering your raw data, now you need to design your Reporting Format. Remember this is the format whose sheet name is Report, that everyone will view it. And try to keep attractive so that, report looks simple and great. My format is here:
Due to Array Formula not supports Marge Cell, that is why Gray Box placed below Phone Specification.
Coding in "Report" Sheet:
Based on Lookup Value F2 (Brand) and G2 (Model) you have to return the related information in C4, C5, C6 and C7 cell first. But the First thing is first. Create a Drop Down List in F2 cell from Data | Data Validation for all of your Brands. I've created here only for Motorola Brand.
With the same way Create another Drop Down List in G2 cell from Data | Data Validation for all of your models. Choose Motorola in F2 cell and Moto X4 in G2 cell.
Now you can use Index and Double Match Array Formula to return your expected data into C4, C5, C6 and C7 cell. For C4 cell use the below formula and press CTRL+SHIFT+ENTER due to it is an Array formula:
For C5 cell:
For C6 Cell:
For C7 Cell
In C9 Cell where Image will return, Copy Moto X4 image from RawData and Paste this image in Report sheet within the box. Remember C9 cell is not a Marge Cell and wide enough more than the image:
Link the Image with Index-Match Formula:
The most important part is to create a link in between Image and Index-Match formula. To do this, just Click on the image in C9 cell to Select it and then go to Formula | Name Manager | New. Then a New Name dialog box will appear.
Type a name for the image box like, "ShowImage" and in Refers To box, use an Index-Match function to return the image from RawData sheet like below:
It would return the Name Manager dialog box. Click on Close button. The final thing to do is, Select the Image by Clicking on Moto X4 image. Then Click on Formula bar and type =ShowImage and press Enter. After successfully done it, the Image of Moto X4 will looks like below image:
Change the Model from G2 Drop Down list, then the Image of C9 cell will automatically change.
My Simple Project:
To do this, I would like to design a project first. My project is, for analyzing purpose I need to see the Phone Specification of many Brands and their many models. Assume that, your Management asked for a report where, they will just select a Model Name, and the details will display including image. To make this example realistic, I have used Motorola Mobile Phone Brand and their few models and features, which I've collected from www.gsmarena.com. My raw data sheet format are given as below but it would be better if you gather more data of all Brands and their models with image and BDT Price. Please note that, RawData is the Sheet name where raw data gathered.
Image 1: Raw Data format
Report Format:
After gathering your raw data, now you need to design your Reporting Format. Remember this is the format whose sheet name is Report, that everyone will view it. And try to keep attractive so that, report looks simple and great. My format is here:
Image 2: Report Format
Due to Array Formula not supports Marge Cell, that is why Gray Box placed below Phone Specification.
Coding in "Report" Sheet:
Based on Lookup Value F2 (Brand) and G2 (Model) you have to return the related information in C4, C5, C6 and C7 cell first. But the First thing is first. Create a Drop Down List in F2 cell from Data | Data Validation for all of your Brands. I've created here only for Motorola Brand.
Image 3: Data Validation List
With the same way Create another Drop Down List in G2 cell from Data | Data Validation for all of your models. Choose Motorola in F2 cell and Moto X4 in G2 cell.
Now you can use Index and Double Match Array Formula to return your expected data into C4, C5, C6 and C7 cell. For C4 cell use the below formula and press CTRL+SHIFT+ENTER due to it is an Array formula:
=INDEX(RawData!$D:$D,MATCH(Report!F2&Report!G2,RawData!A:A&RawData!B:B,0))
For C5 cell:
=INDEX(RawData!$E:$E,MATCH(Report!F2&Report!G2,RawData!A:A&RawData!B:B,0))
For C6 Cell:
=INDEX(RawData!$F:$F,MATCH(Report!F2&Report!G2,RawData!A:A&RawData!B:B,0))
For C7 Cell
=INDEX(RawData!$G:$G,MATCH(Report!F2&Report!G2,RawData!A:A&RawData!B:B,0))
In C9 Cell where Image will return, Copy Moto X4 image from RawData and Paste this image in Report sheet within the box. Remember C9 cell is not a Marge Cell and wide enough more than the image:
Image 4: Image Pasted in C9 cell from RawData
Link the Image with Index-Match Formula:
The most important part is to create a link in between Image and Index-Match formula. To do this, just Click on the image in C9 cell to Select it and then go to Formula | Name Manager | New. Then a New Name dialog box will appear.
Type a name for the image box like, "ShowImage" and in Refers To box, use an Index-Match function to return the image from RawData sheet like below:
=INDEX(RawData!$C:$C,MATCH(Report!$G$2,RawData!$B:$B,0))
Image 5: Creating a Name "ShowImage"
It would return the Name Manager dialog box. Click on Close button. The final thing to do is, Select the Image by Clicking on Moto X4 image. Then Click on Formula bar and type =ShowImage and press Enter. After successfully done it, the Image of Moto X4 will looks like below image:
Image 6: Linked the Name Box with Image
Change the Model from G2 Drop Down list, then the Image of C9 cell will automatically change.