Match/Index/Lookup - Searching From Bottom to Top (A reverse lookup maybe)

**Neutralizer** · 05-02-2013, 02:17 AM

Hi All,

I'm hoping someone can help me make this work. My sample data is as follows:

A1:A6 = ("Letters",a,b,c,d,c)
B1:B6 = ("Numbers",20,30,40,50,60)
So basically two columns, one with the header "Letters" and one with the header "Numbers" with their respective content below them:
Letters | Numbers
a | 20
b | 30
c | 40
d | 50
c | 60

Next you need to accept that this data cannot be sorted before it is searched due to the way other people are using the source data in this sample table.

Now, I could use a forumla like the one below and it would return the value 40 (the first match for "c").

Index(A1:B6, Match("c",A1:A6,0), Match("Numbers",A1:B1,0))

But, there are two "c" values in Column A, I'm looking for something that will return the second match and it's corresponding value (60). Does anyone know a robust formula for doing this?

The following formula achieves this:

=LOOKUP(COUNTIFS($A$1:$A$6,"c"),1/($A$1:$A$6="c"),$B$1:$B$6)

But:
1. It's not as robust as Index(Match,Match) is when it comes to having columns and rows added
2. I don't understand how it works, specifically this bit:

1/($A$1:$A$6="c")

How does that produce a [Lookup_Vector] for the lookup function?

Can anyone help me to 'merge' these two formulas into a robust solution to lookup the bottom most value in a table?

Thanks!

**Neutralizer** · 05-02-2013, 02:26 AM

Hmmm, so I have produced one that works, but it's fairly hideous:

=INDEX($A$1:$B$6,LOOKUP(COUNTIFS($A$1:$A$6,"c"),1/($A$1:$A$6="c"),ROW($A$1:$A$6)),MATCH("Numbers",$A$1:$B$1,0))

I still don't understand how the middle argument in the Lookup function works though

**dhn46** · 05-02-2013, 02:56 AM

Do you want to lookup the bottom most value in a table?
Your data set A4:B8 area
You try this Fomula:

Please Login or Register  to view this content.

**Neutralizer** · 05-02-2013, 03:04 AM

Yep, the formula I have come up with will look up the bottom most value within the indexed range already, which is due to the COUNTIFS function that is in there.
If you replaced the COUNTIFS with a reference to a variable or whatever you could fairly easily make it return the Nth specified value in a table as well.

It's also fairly robust since it matches based on values in column headers (and a row), so as long as the specified index range is sufficiently large for all the data and there are no identical column headers, it should always return the value you want at the intersection of the two.
It does need a little modification though if your table does not start in the first row. Say your headers are actually in row 6, you need to manipulate the outcome of the LOOKUP that resides within the Index functions row argument (but its fairly easy to do this just by subtracting the row number of the headers from the result).