|
Excel
Formula |
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
Table A - Overview |
|
|
|
|
|
|
|
|
|
|
# |
Title |
|
|
|
|
|
|
|
|
|
|
Line |
|
|
1 |
Find a specific row in a
table |
|
|
|
|
|
|
|
39 |
|
|
2 |
Vlookup |
|
|
|
|
|
|
|
|
|
62 |
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
Table B - (Code) Details |
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
# |
Details |
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
1 |
Find a specific row in a
table |
|
|
|
|
|
|
|
Top |
|
|
Put table below in cells A1..D6 |
|
|
Table |
|
|
Type |
Color |
Size |
Name |
|
|
Cat |
White |
Small |
Jim |
|
|
Cat |
Red |
Large |
John |
|
|
Cat |
Black |
Small |
Anita |
|
|
Dog |
White |
Small |
Mike |
|
|
Dog |
Black |
Large |
Tim |
|
|
|
Dog |
White |
Small |
|
|
|
Question: |
Find name of small, white, dog |
|
|
|
Answer: |
INDEX($d2:$d$6,MATCH(a8&b8&c8,$A$2:$A$5&$B$2:$B$5&$C2:$C6,0)) |
|
|
|
Mike |
|
|
Note: Use
{Ctrl}{Shift}{Enter} because of array function |
|
|
|
|
See
also: |
https://support.microsoft.com/en-ie/help/214142/how-to-use-the-index-and-match-worksheet-functions-with-multiple-crite |
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
2 |
Vlookup |
|
Top |
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|