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

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

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!

2. ## Re: Match/Index/Lookup - Searching From Bottom to Top (A reverse lookup maybe)

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

3. ## Re: Match/Index/Lookup - Searching From Bottom to Top (A reverse lookup maybe)

Do you want to lookup the bottom most value in a table?
You try this Fomula:
``Please Login or Register  to view this content.``

4. ## Re: Match/Index/Lookup - Searching From Bottom to Top (A reverse lookup maybe)

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).

5. ## Re: Match/Index/Lookup - Searching From Bottom to Top (A reverse lookup maybe)

Another alternative is this, but be aware that it uses the evil of a volatile function (OFFSET):
=LOOKUP(COUNTIFS(\$A\$1:\$A\$6,"c"),1/(\$A\$1:\$A\$6="c"),OFFSET(\$A\$1,0,MATCH("Numbers",\$A\$1:\$B\$1,0)-1,COUNTA(A1:A6)-1))

There are currently 1 users browsing this thread. (0 members and 1 guests)

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1