# Lookup using relative reference

1. ## Lookup using relative reference

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!!

2. ## Re: Lookup using relative reference

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

3. ## Re: Lookup using relative reference

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!

4. ## Re: Lookup using relative reference

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)), "")

5. ## Re: Lookup using relative reference

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

6. ## Re: Lookup using relative reference

or
D2=IF(B2="Cat","",IF(B2=B1,D1,C1))
``Please Login or Register  to view this content.``

7. ## Re: Lookup using relative reference

Originally Posted by 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
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!

##### Users Browsing this Thread

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