Thread: How to "lookup"

I am so confused with the lookup function. Here is an example:

I have a table with, let's say 3 main category, in each I have different subcategories:








A_________B

Seafood Fish
Shrimp
Octopus

Car Honda
BMW
Toyota

Drink Wine
Sprite
Beer



Now I type in a SubCategory like "Beer". I want a formular for the next cell will give me the main categogy (Drink).

Thank you,

If the above table starts at A1, and say you enter Beer into D1, then try:

=LOOKUP(REPT("z",255),$A$1:INDEX($A:$A,MATCH($D1,$B:$B,0)))

So if "Shrimp" is in A11, then in B11:

=LOOKUP(REPT("z",255),$A$1:INDEX($A$1:$A$9,MATCH($A11,$B$1:$B$9,0)))

it looks for the last text string in A1:A9 up to the point where a match is found to A11 in B1:B9.

Well, it works. It will really help if you explain the formula for me
TKS A LOT!!!

As mentioned, it looks for the last text string in A1:A9 up to the point where a match is found to A11 in B1:B9.

So first this part: MATCH($A11,$B$1:$B$9,0) finds the matching row number for "Shrimp" in B1:B9

Then: INDEX($A$1:$A$9,MATCH($A11,$B$1:$B$9,0)) indexed the cell that the match is found against corresponding cell in A1:A9

Then: $A$1:INDEX($A$1:$A$9,MATCH($A11,$B$1:$B$9,0)) creates the range to search for your column A headers. The range ending at the row where the match to "Shrimp" was found so in this case the range would be $A$1:$A$2 since "Shrimp" is found in Row2 of the table.

This: LOOKUP(REPT("z",255) looks for the last row in that contains text in the range: $A$1:INDEX($A$1:$A$9,MATCH($A11,$B$1:$B$9,0))

REPT("z",255) creates a string of 255 z's. LOOKUP() looks for the last occurance of text in a range that is smaller than or equal to the lookup value... so we are looking for a string of 255 z's in A1:A9. It obviously cannot find that so it returns the last string in the same range, which is always the last string to be smaller than or equal to 255 z's.