x
• Status: Solved
• Priority: Medium
• Security: Public
• Views: 286

# Lookup Value and Return Value from Different Row

Hello Experts -

I need to search Spr!A9:A153 for the value in cell A25 and return the value in the cell below it and beside it.  Then for the next 5 rows, I need the data another row down.  Attached in an example with 3 tabs, Spr, Lamar, CO and Example.  Lamar, CO is what I am getting, Example is what the end result needs to be.

I have tried using the offset and match functions and those work, but only for the 1st row, I cant figure out how to get the rows below that.
OffsetMatchExample.xlsm
0
rsburge
• 2
1 Solution

Commented:
In A19:

=INDEX(Spr!\$A\$8:\$A\$162,MATCH(\$A\$18,Spr!\$A\$8:\$A\$162,0)+ROWS(\$A\$18:\$A18))

copied down 5 rows

in C18:

=INDEX(Spr!\$C\$8:\$C\$162,MATCH(\$A\$18,Spr!\$A\$8:\$A\$162,0)+ROWS(\$A18:\$A\$18)-1)

copied down 6 rows.

similar in A26, C27 changing the \$A\$18 reference to suit.
0

Author Commented:
Thank you so much!

This works perfectly with one minor change...

for my needs, the formula in C18 ends up needing to be

=INDEX(Spr!\$C\$8:\$C\$162,MATCH(\$A\$18,Spr!\$A\$8:\$A\$162,0)+ROWS(\$A\$18:\$A18),0)

and then copy it down.  I am not sure why this works, but it does.  :)
0

Commented:
Notice that C18 is up one row from A19, so I figured you were looking for the subtotal value for Lamar, CO  -  Day 1  -  Saturday, Apr 5, 2014
... but Ok.  Thanks for the feedback.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

## Featured Post

• 2
Tackle projects and never again get stuck behind a technical roadblock.