excel's vlookup() - can you look up 2 rows vs the normal 1 row? 4

[kanin247]

**This question extends my previous question in the forum (subject line, "To Whom It May Concern: I'm trying...&quot.

To Whom It May Concern:
I know that vlookup() gets values for occurrences of a specified row but can it also return values of data located on the next row? These values are a continuation of the sought data. Take for example the following:

Area# From To
1 Bus 10 Bus 11 //Line 1
Bus 10 Bus 12 //Line 2
2 Bus 15 Bus 25 //Line 3
Bus 8 Bus 10 //Line 4
3 Bus 3 Bus 5 //Line 5
Bus 4 Bus 9 //Line 6

So, normally, using vlookup() will allow me to get the a specific row of data (Line 1,3,5). But I also need it to return the additional row of data (Line 2,4,6). Is this possible to do using vlookup? If so, how? Or is there another function that can handle my situation?

I would appreciate any help. Thanks.

kanin

 

[xlbo]

I reckon a combination of INDEX / MATCH and OFFSET will be required - or a UDF - there's no way VLOOKUP will do this as it returns only the value, not any kind of cell reference
Rgds
~Geoff~

 

[rmikesmith]

kanin,

Geoff is right, of course. Assuming your data setup (info in range A1:C7 w/ headers in row 1):

To get the "To" data for Area 2, Row 4, you would use VLookup:

=VLookup(2,A2:C7,3,FALSE)

To get the "To" data for Area 2, Row 5, use the following:

=INDEX(A2:C7,MATCH(2,A2:A7,0)+1,3)


Hope this helps.
M. Smith

 

[DaleWatson123321]

kanin,

I've developed an example file using the DGET function, and it works to your specifications.

For this to work, however, it requires the following:

1) Your list must be a UNIQUE list of Area #'s

2) You would need to add an "identifier" to the second row for each Area #.

3) Preferably, the area number should be formatted as TEXT, and the second row of each Area # would be, for example: 1a, 2a, 3a, 4a, etc.

This example file is set up with only ONE entry cell for the Area number. Immediately after entering the Area #, the 4 DGET formula cells below it produce the results you specify - i.e. for Area 3:
Bus 3, Bus 5
Bus 4, Bus 9.

If this can work for you, I can email you the example file.

Regards, ...Dale Watson dwatson@bsi.gov.mb.ca

 

[xlbo]

Mike - nice one
I knew it had to be possible with INDEX and MATCH but to my shame I've not used them much. Really should do as in combination, they are VERY powerful
cheers for providing the answer that I didn't have time to work out ;-) Rgds
~Geoff~

 

[DaleWatson123321]

Mike,

Indeed your solution is "PERFECT". It also serves as a reminder that we should not overlook those "other" functions available but not used as often.

*** A STAR to you ***

Regards, ...Dale Watson dwatson@bsi.gov.mb.ca

 

[kanin247]

Mike,
Your solution worked beautifully. Thank you.

And thanks to everyone else who provided a response. Everything said was very much useful and extrememly helpful. I gave every a star for their time and effort.

Thanks again!

kanin

 

[rmikesmith]

Thanks, Gents.

My biggest "problem" is a tendency to reach for the VBA solution rather than explore a built-in function solution. I'm trying to work on that, but it's tough. Credit to you for the nudge, Geoff.


Regards,
Mike