LOOKUP next to last entry

[Titian]

This formula operates correctly in that it returns the matching value from the concatination of cells H2&G5 in column "B" and returns the value off the same row in column "C".

=LOOKUP(2,1/(B:B=H2&G5),C:C)

Can the formula be amended to return the penultimate matching value?

Thanks in advance

 

[T. Valko]

This formula operates correctly in that it returns the matching value from the concatination of cells H2&G5 in column "B" and returns the value off the same row in column "C".


=LOOKUP(2,1/(B:B=H2&G5),C:C)


Can the formula be amended to return the penultimate matching value?

Thanks in advance

Try something like this...

With your data in the range B1:C20...

Array entered**:

=INDEX(C:C,LARGE(IF(B1:B20=H2&G5,ROW(B1:B20)),2))

** array formulas need to be entered using the key
combination of CTRL,SHIFT,ENTER (not just ENTER).
Hold down both the CTRL key and the SHIFT key
then hit ENTER.

 

[Titian]

Thanks Biff, that does the job.

One question, if a second occurrence is not present in the data list an error is returned, #NUM!, how's best to trap the error and return "only one"?

Thanks again.

 

[T. Valko]

Thanks Biff, that does the job.

One question, if a second occurrence is not present in the data list an error is returned, #NUM!, how's best to trap the error and return "only one"?

Thanks again.

If you're using Excel 2007 or later...

Still array entered...

=IFERROR(INDEX(C:C,LARGE(IF(B1:B20=H2&G5,ROW(B1:B20)),2)),"Only One")

 

[Titian]

Great that fixed it.

Thanks for your help.

 

[T. Valko]

Great that fixed it.

Thanks for your help.

You're welcome. Thanks for the feedback!