vlookup-1 +1 3

[SQLScholar]

Hey all,

Say if a Vlookup with a range lookup of true, comes up with answer on row 98, can you do something that would bring back answer 99 or indeed 97. So basically i want the row before and the row after.

Any ideas?

Dan

----------------------------------------
There are 2 types of computer, the prototype and the obsolete!!

 

[Adnane]

if i got it right, you could maybe try this:

say you will get the results on three columns.
you could have on B1 =vlookup($A1,my_range,column(CS1),false)

this way, if you drag the formula on the two other columns, you will get in B1 the the value in column 97, in C1 98, and in D1 99.

Hope this helps

Adnane

Micorosft North Africa

http://www.microsoft.com/northafrica

 

[KenWright]

Combination of INDEX/MATCH would normally do that for you, if you want the value on the row before or after, or just MATCH if you simply want the row number:-

=MATCH(B1,A1:A1000,0)+1

will give you the row with the matched value plus 1, or

=INDEX(A1:A1000,MATCH(B1,A1:A1000,0)+1)

if you want to match the value in B1 and then return the value below it

Regards
Ken...............

----------------------------------------------------------------------------
It's easier to beg forgiveness than ask permission

----------------------------------------------------------------------------

 

[SkipVought]

Hi,

Use the Match and Index functions...
[tt]
=IF(ISERROR(Match(LookupValue,FirstColumn,0)),"",Index(OtherColumn,Match(LookupValue,FirstColumn,0)-1,1))

=IF(ISERROR(Match(LookupValue,FirstColumn,0)),"",Index(OtherColumn,Match(LookupValue,FirstColumn,0)+1,1))
[/tt]


Skip,

Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884

 

[xlbo]

Not with vlookup - vlookup returns a value as opposed to a range so it cannot be offset in any way.

You can however, use OFFSET with a combination of INDEX & MATCH

=INDEX(Sheet2!B:B,MATCH(Sheet1!A:A,Sheet2!A:A,1))
This formula works in a similar way to VLOOKUP in that it returns data based on a match - this would return the value in col B, sheet2 where colA sheet2 matches colA sheet1. If you wrap this with the OFFSET function, you can then return rows of data up or down from the match - the following would return the figure in ColB on sheet2, 1 row UP from the row that matches sheet1 to sheet2

=OFFSET(INDEX(Sheet2!B:B,MATCH(Sheet1!A:A,Sheet2!A:A,1)),-1,0)

Rgds, Geoff

Never test the depth of water with both feet

Help us to help you by reading FAQ222-2244 before you ask a question