You know VLOOKUP, one of the most loved function of Excel. The syntax is VLOOKUP(lookup_value,table_array,col_index_num,range_lookup)
Here look_value can be a single value not multiple values.
Now, you are having a situation where you want to do vlookup with more than 1 values. For the purpose of illustrating the concept, let's say we have 2 values to be looked up.
Below is your lookup table and you want to look up for Emp – H and Gender – F for Age.
=INDEX(C2:C12,MATCH(1,INDEX(–((A2:A12=F2)*(B2:B12=G2)*(ROW(A2:A12)-ROW(A2)+1)<>0),,),0))
Concatenation Approach
=INDEX(C2:C10,MATCH(F2&"@@@"&G2,INDEX(A2:A10&"@@@"&B2:B10,,),0))
@@@ can be replaced by any characters which should not be part of those columns.
By concatenation, you can have as many columns as possible.
CAUTION – Result of entire concatenation should not be having length more than 255. Hence, F2&"@@@"&G2 should not have more than 255 characters.
Another alternative is to use below Array formula –
=INDEX(C2:C12,MATCH(1,–NOT(ISLOGICAL(IF(A2:A12=F2,IF(B2:B12=G2,C2:C12)))),0))
Note – Array Formula is not entered by pressing ENTER after entering your formula but by pressing CTRL+SHIFT+ENTER. If you are copying and pasting this formula, take F2 after pasting and CTRL+SHIFT+ENTER. This will put { } brackets around the formula which you can see in Formula Bar. If you edit again, you will have to do CTRL+SHIFT+ENTER again. Don't put { } manually.