# Thread: Looking up (XP)

1. ## Looking up (XP)

How can I do a lookup when the reference is not in either the same row or column? In the attached example, I want to return the value in B2 (4567) by looking up the value in A1 (12345).

Thanks!

2. ## Re: Looking up (XP)

What are the lookup criteria? Since only A1 and B2 are populated, it is not clear what you want. Could you provide a more complete description?

3. ## Re: Looking up (XP)

If A2 were popluated with 4567, I know I could hlookup(A2,\$A:\$A,1,false) to return the value in A2. If B1 were populated with 4567, I know I could vlookup(A1,\$A:\$B,2,false) to return the value in B1.

However, I'm trying to do a lookup based on a reference that isn't in the same row or column of the reference. What I should have mentioned, is that the result is always 1 row down and 1 column across for the referenced cell. Hope that makes more sense now.

4. ## Re: Looking up (XP)

Let's say that you enter the lookup value in cell E1. Enter the following formula in E2 (or where you need it):
<code>
=OFFSET(\$A\$1,MATCH(E1,\$A:\$A,0),1)
</code>
MATCH(E1,\$A:\$A,0) looks for an exact match of the value of E1 in column A, returning the index (row number) of the match, or #N/A if there is no match.
The OFFSET function starts at cell A1, moves the number of rows returned by MATCH down, and one column to the right, and returns the value of that cell.

See attached version

5. ## Re: Looking up (XP)

Got it! I thouht that I had to use offset() and match() somehow but after hours of trying to "get it" myself, your reply is really appreciated.

6. ## Re: Looking up (XP)

Hi there

without further information I am assuming you are looking for a value, say 12345 and then wanting to offset by 1 column and then 1 row. Try putting this formula in a cell (say F2 in your example and place 12345 in E2):

=OFFSET(\$A\$1,MATCH(E2,\$A\$1:\$A\$4,0),1,1)

Is that what you are looking for?

7. ## Re: Looking up (XP)

oops that will teach me to go off line and then sort a question out...make <img src=/S/note.gif border=0 alt=note width=20 height=20> to myself, check before posting <img src=/S/grin.gif border=0 alt=grin width=15 height=15>

#### Posting Permissions

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