Hi all,
My name's Grady, I'm working on Excel 365 on Windows.
I'm trying to create an image lookup column that will insert/match images in one list (e.g. an inventory) from a master catalog.
Right now I'm troubleshooting on a single worksheet (XL2BB Below).
1. I started with the INDEX MATCH method for switching images based on the content of a single cell (often demonstrated as a drop down)
ex. =INDEX(Sheet1!$B$3:$B$5, MATCH(Sheet1!$A$13,Sheet1!$A$3:$A$5))
(applied directly to a target pic using a named formula)
This works fine for flipping pictures and matching descriptions (when the INDEX range is adjusted)
2. I replaced the direct cell reference with an INDIRECT to move toward what I'm actually trying to do
ex. =INDEX(Sheet1!$B$3:$B$5, MATCH(INDIRECT("Sheet1!$A$20"),Sheet1!$A$3:$A$5))
This also worked fine.
3. Finally, I used ROW() to feed the INDIRECT function to get a row specific lookup.
ex. =INDEX(Sheet1!$B$3:$B$5, MATCH(INDIRECT("Sheet1!$A$"& TEXT(ROW(), 0)),Sheet1!$A$3:$A$5))
This did not work. It flips the picture, but not as expected.
It does however work for matching description (again when the INDEX range is adjusted).
I'm hoping that I'm missing something simple. Any help will be greatly appreciated!
p.s. I'm tracking another method of flipping pictures using INDIRECT and linked pictures. I'm not using that method because of the requirement to name cells. My inventory list is pretty long and has spaces and dashes in names that get replaced with underscores when Naming by Selection.
My name's Grady, I'm working on Excel 365 on Windows.
I'm trying to create an image lookup column that will insert/match images in one list (e.g. an inventory) from a master catalog.
Right now I'm troubleshooting on a single worksheet (XL2BB Below).
1. I started with the INDEX MATCH method for switching images based on the content of a single cell (often demonstrated as a drop down)
ex. =INDEX(Sheet1!$B$3:$B$5, MATCH(Sheet1!$A$13,Sheet1!$A$3:$A$5))
(applied directly to a target pic using a named formula)
This works fine for flipping pictures and matching descriptions (when the INDEX range is adjusted)
2. I replaced the direct cell reference with an INDIRECT to move toward what I'm actually trying to do
ex. =INDEX(Sheet1!$B$3:$B$5, MATCH(INDIRECT("Sheet1!$A$20"),Sheet1!$A$3:$A$5))
This also worked fine.
3. Finally, I used ROW() to feed the INDIRECT function to get a row specific lookup.
ex. =INDEX(Sheet1!$B$3:$B$5, MATCH(INDIRECT("Sheet1!$A$"& TEXT(ROW(), 0)),Sheet1!$A$3:$A$5))
This did not work. It flips the picture, but not as expected.
It does however work for matching description (again when the INDEX range is adjusted).
I'm hoping that I'm missing something simple. Any help will be greatly appreciated!
p.s. I'm tracking another method of flipping pictures using INDIRECT and linked pictures. I'm not using that method because of the requirement to name cells. My inventory list is pretty long and has spaces and dashes in names that get replaced with underscores when Naming by Selection.
| Test Book.xlsx | |||||||||
|---|---|---|---|---|---|---|---|---|---|
| A | B | C | D | E | F | G | |||
| 1 | SOURCE DATA | ||||||||
| 2 | ID | Image | Desc | ||||||
| 3 | ID1 | Description 1 | |||||||
| 4 | ID2 | Description 2 | |||||||
| 5 | ID3 | Desription 3 | |||||||
| 6 | |||||||||
| 7 | |||||||||
| 8 | |||||||||
| 9 | |||||||||
| 10 | Single INDEX/MATCH Example | =INDEX(Sheet1!$B$3:$B$5, MATCH(Sheet1!$A$13,Sheet1!$A$3:$A$5)) | |||||||
| 11 | Works for picture | ||||||||
| 12 | Also works for description when range adjusted | ||||||||
| 13 | ID2 | Description 2 | |||||||
| 14 | |||||||||
| 15 | |||||||||
| 16 | |||||||||
| 17 | INDEX/MATCH using INDIRECT to get Single ID | =INDEX(Sheet1!$B$3:$B$5, MATCH(INDIRECT("Sheet1!$A$20"),Sheet1!$A$3:$A$5)) | |||||||
| 18 | Works for picture | ||||||||
| 19 | Also works for description when range adjusted | ||||||||
| 20 | ID3 | Desription 3 | |||||||
| 21 | |||||||||
| 22 | |||||||||
| 23 | |||||||||
| 24 | INDEX/MATCH by Row (Doesn't Work) | =INDEX(Sheet1!$B$3:$B$5, MATCH(INDIRECT("Sheet1!$A$"& TEXT(ROW(), 0)),Sheet1!$A$3:$A$5)) | |||||||
| 25 | DOESN'T work for picture | ||||||||
| 26 | BUT DOES work for description when range adjusted | ||||||||
| 27 | ID1 | Description 1 | ID1 | 1 | |||||
| 28 | Indirect function | Match function | |||||||
Sheet1 | |||||||||
| Cell Formulas | ||
|---|---|---|
| Range | Formula | |
| C13 | C13 | =INDEXMATCHDESC |
| C20 | C20 | =INDEXMATCHDESC_INDIRECT |
| C27 | C27 | =INDEXMATCHDESC_INDIRECT_BYROW |
| E27 | E27 | =INDIRECT("Sheet1!$A$"& TEXT(ROW(), 0)) |
| G27 | G27 | =MATCH(INDIRECT("Sheet1!$A$"& TEXT(ROW(), 0)),Sheet1!$A$3:$A$5) |
| Named Ranges | ||
|---|---|---|
| Name | Refers To | Cells |
| INDEXMATCHDESC | =INDEX(Sheet1!$C$3:$C$5, MATCH(Sheet1!$A$13,Sheet1!$A$3:$A$5)) | C20, C27, C13 |
| INDEXMATCHDESC_INDIRECT | =INDEX(Sheet1!$C$3:$C$5, MATCH(INDIRECT("Sheet1!$A$20"),Sheet1!$A$3:$A$5)) | C20, C27, C13 |
| INDEXMATCHDESC_INDIRECT_BYROW | =INDEX(Sheet1!$C$3:$C$5, MATCH(INDIRECT("Sheet1!$A$"& TEXT(ROW(), 0)),Sheet1!$A$3:$A$5)) | C20, C27, C13 |
