Find second vlookup value

Question

I have the following vlookup formula in Cell J2 of sheet 'SKU DATA' ...

=VLOOKUP(A2,DUPLICATE!C:AJ,34,0)

But, in column 34 of the 'DUPLICATE' sheet range there are 2 possible results (there will only ever be a maximum of 2 results).

Vlookup shows the first result ... is there a formula that I can use in cell J3 to show me the second result?

I've looked at several match, index and small functions but, can't get anything to work ?

Thanks

Answer 1

Here's my answer using an array formula (CTRL+SHIFT+ENTER or CSE - make sure you see the {}):

I like this approach because you can change the second to last number to match whatever occurrence you are looking for. For example, if you want the third match, you change the 2 to a 3.

=INDEX($B$2:$B$13,SMALL(IF($A$2:$A$13=D2,ROW($A$2:$A$13)-ROW($A$2)+1,ROW($A$13)+1),2),0)

More plainly:

=INDEX(DesiredValues,SMALL(IF(PossibleMatches=MyMatch,ROW(PossibleMatches)-Row(FirstRow)+1,ROW(LastRow)+1),nth_match),thisColumn)

In practice:

Answer 2

I'm sure there are MANY ways to do this (and this may not be the most efficient), but the first that comes to mind would be to find the first match in column C of your Duplicate sheet and then start your VLOOKUP() from there.

That would look as follows:

=VLOOKUP(A2,OFFSET(DUPLICATE!$C$1,MATCH(A2,DUPLICATE!C:C,0),0,1000,34),34,0)

In essence:

1. The MATCH() function will match the first occurrence and return that row number
2. The OFFSET() then creates a range reference starting at column C, but 1 row after that match and 1000 rows deep and 34 columns wide
3. You then do your Vlookup in that range

This will return the second reference.

Hope that makes sense / does the trick!

Answer 3

Here's a normal formula for return the 2nd matched value

In E2, copied down :

=INDEX(B:B,MMULT(SMALL((A$1:A$13<>D2)/1%%+ROW($1:$13),2),1))