The most popular way to do a two-way lookup in Excel is by using INDEX MATCH MATCH. To retrieve multiple matching values from a set of data with a formula you can use the IF and SMALL functions to figure out the row number of each match and feed that value back to INDEX. INDEX data_array MATCH vlookup_value lookup_column_range 0 MATCH hlookup value lookup_row_range 0.
If we want to get value from the adjacent cells value of nth match in range names A2.
INDEX amtsSMALLIF ids idROW ids - ROWINDEX ids11 1 H6. MIN number1 number2 Number1 number2 Number1 is an optional argument. INDEX arraySMALLIF vals valROW vals-ROWINDEX vals111 nth To get the nth match using INDEX and MATCH you can use an array formula that uses the IF and SMALL functions to figure out the row number of the match. Especially since you are using a recent version of Excel it is best not to use whole column references if you dont need to.