Work with the example
Can a lookup return the folder name when the key is physically to its right? A5:A7 hold names; D5:D7 hold the unique supplied keys in unsorted order. G5 chooses K1. H5 finds its position and I5 reads that position from the names.
| Folder | Key |
|---|---|
| Blue folder | K3 |
| Red folder | K1 |
| Green folder | K2 |
Edit the working cells
H5 uses =MATCH(G5;D5:D7;0) and returns 2. I5 uses =INDEX(A5:A7;H5) and returns Red folder. Change A6 to Red binder: I5 updates. A fresh query K2 returns position 3 and Green folder. Query K9 produces #N/A rather than an invented name.
Check the failure case
I8 uses VLOOKUP against A5:D7, whose first column contains names rather than keys. It fails to find K1. If rearranging the table, move complete key/name pairs together; do not sort one of the separated columns alone.
What does the final 0 in MATCH request?
Show the worked answer
An exact match, so the unsorted key order is acceptable for these unique keys.