If you are looking for the 2nd instance of the lookup value, then you can do without the helper column. Instead, create the table array dynamically by using the INDIRECT function together with MATCH: =VLOOKUP(E1, INDIRECT("A"&(MATCH(E1, A2:A11, 0)+2)&":B11"), 2, FALSE) Where: E1 is the lookup value A2:A11 is the lookup range

To find the second match value, please change the above formula to =INDEX ($B$2:$B$6,SMALL (IF ($D$1=$A$2:$A$6,ROW ($A$2:$A$6)-ROW ($A$2)+1),2)), and then press Ctrl + Shift + Enter keys simultaneously.

A simple solution to this problem would be sorting our data on sales person’s name. That way all Johns would line up one beneath another. And we just have to find the first John’s position and add 1 to it to get to 2nd occurrence. Like this =MATCH("John", C5:C17, 0) + 1

To retrieve information from a table based on a partial match, you can use the VLOOKUP function in exact match mode with a wildcard. In the example shown, the formula in H7 is: = VLOOKUP (value & "*", data,2,FALSE) where value (H4) and data (B5:E104) are named ranges.

The required formula in Cell C18 will be: =VLOOKUP(D15&D16,B5:G13,MATCH(B18,B4:G4,0),FALSE) The MATCH function looks for the value present in Cell B18 in the array of B4:G4 and then returns the column number. This column number is then assigned to the third argument (col_num_index) of the VLOOKUP function.