Improve company productivity with a Business Account.Sign Up

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

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
Asked:
rsburge
  • 2
1 Solution
 
NBVCCommented:
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
 
rsburgeAuthor 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
 
NBVCCommented:
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.

Join & Write a Comment

Featured Post

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

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