Lookup using relative reference

[renospread]

Hi everyone!

I have a problem using VLOOKUP...

For the Meow rows, I want to return the number of Cat (which is 1 or 4 or ... depending on which is above it).
The data is arranged as shown below..

1 Cat
2 Meow 1
3 Meow 1
4 Cat
5 Meow 4
6 Meow 4


What I need for a function to do is to: LOOKUP the number of the cat that is NEAREST ABOVE the meow.
Hope you guys could help. Thanks!!

[ThirdFret]

The following is a solution that uses ARRAY formulas. The formula must be entered by pressing Ctrl+Shift+Enter instead of just Enter.

Assuming your numbers start in cell A1 and continue down column A, and your Cats and Meows start in B1 and continue down column B, the following ARRAY formula in cell C1 and dragged down column C will give you your desired result.

Please Login or Register  to view this content.

If the formula is returning 0's and blanks, you haven't entered the formula using Ctrl+Shift+Enter. You'll know you have done it correctly when you see the curly brackets around the entire formula {}.

Let me know if that helps!

Cats.png

[renospread]

Hi ThirdFret! Thanks for the response! However, the numbers of the cats are actually huge randomized numbers, so I can't use the "LARGE" function.

Here's a better example:
987654321 Cats
1 Meow 987654321
2 Meow 987654321
789 Cats
26 Meow 789
36 Meow 789

What I want the function to do: Go to the left of Meow and Lookup the cat number nearest above it.
So far, there's OFFSET and LOOKUP. But I don't know how to combine them. Or maybe there are other ways.
Hope you guys could help. Thanks!

[renospread]

Hi again ThirdFret! Really grateful for your reply!
I already figured it out using your formula and some index-match!!! Yey. Thank you so much. :D
Here's my output below:

Cats.png

To those who have the same problem,
first, number your cats in a separate column, say column A
second, use the formula on column D: =IF(B1="Cat","",LARGE(IF($B$1:B1="Cat",$A$1:A1,0),1))
third, use index-match to get the randomized numbers on column E: =IFERROR(INDEX($C:$C, MATCH($D1,$A:$A, 0)), "")

[samba_ravi]

D1=IF(B1="Cat","",LOOKUP(2,1/(B$1:B1="Cat"),C$1:C1))

Please Login or Register  to view this content.

Try this and copy towards down

[samba_ravi]

or
D2=IF(B2="Cat","",IF(B2=B1,D1,C1))

Please Login or Register  to view this content.

[ThirdFret]

Hi again ThirdFret! Really grateful for your reply!
I already figured it out using your formula and some index-match!!! Yey. Thank you so much. :D

Glad I could help! Thanks for posting your final solution to the forum.

If you feel the original question has been answered you should mark the thread as SOLVED, under the Thread Tools menu in the top right.

Cheers!