# Thread: using offset and match (Excel 2002)

1. ## using offset and match (Excel 2002)

I am trying to find the value in one table of data based upon matched values in another table. This seems a good use for offset and match but I can't get the formula to work, I keep getting an error with the formula I have created: OFFSET(MATCH(F1,A1:A12,0),1,1). The worksheet I am trying to use it in is attached. This is a demo sheet. Just tell me what stupid mistake I am making. THanks

2. ## Re: using offset and match (Excel 2002)

The workbook you attached doesn't tell us what you want to accomplish. Can you explain in words what you want to look up? Perhaps an example would make it clearer.

3. ## Re: using offset and match (Excel 2002)

I want to show the value in column B that is one row lower than the value in column A that matches the value in row E1:K1. e.g. find the value "ab" in col. A and then return the result "300" which is the value one row lower in column B. I will need to be able to modify the offset so I can find the value one row higher in another column. thanks

4. ## Re: using offset and match (Excel 2002)

Are you looking for in E3:
<pre>=OFFSET(INDIRECT("A"&E2),1,1)</pre>

or perhaps without using the intermediate formula:
<pre>=OFFSET(INDIRECT("A"&MATCH(F1,\$A\$1:\$A\$12,0)), 1,1)</pre>

Steve

5. ## Re: using offset and match (Excel 2002)

Try a combination of INDEX (instead of OFFSET) and MATCH:
<code>
=INDEX(\$A\$1:\$B\$13,MATCH(F1,\$A\$1:\$A\$13,0)+1,2)
</code>
will look up the value in column B one row down from the row where the value from F1 is found.

6. ## Re: using offset and match (Excel 2002)

that works, thank you

7. ## Re: using offset and match (Excel 2002)

that also works, thank you

#### Posting Permissions

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