Vlookup

[Willie McL]

Hello,

Could anyone help me...

When I enter a vlookup formula, the first matching value is found and
displayed, is there any way of getting the formula to look up the second or
third values, e.g. to look at a trend over a particular week. I have been
able to do this before somehow using these symbols {} but cannot remember
what I done!
Thanks for any suggestions,

Willie

 

[Jason Morin]

One way:

=INDEX(B1:B11,SMALL(IF(A1:A11=F3,ROW(A1:A11)-MIN(ROW
(A1:A11))+1),X))

Array-entered (press ctrl + shift + enter), where:

B1:B11 = range that contains the value to return
A1:A11 = range that contains the value to look up
F3 = holds the value to lookup
X = nth instance to look up

So, for example, this would find the 3rd instance of the
value in F3 in A1:A11, and return the corresponding value
in B1:B11:

=INDEX(B1:B11,SMALL(IF(A1:A11=F3,ROW(A1:A11)-MIN(ROW
(A1:A11))+1),3))

HTH
Jason
Atlanta, GA

 

[Alan Beban]

Hello,

Could anyone help me...

When I enter a vlookup formula, the first matching value is found and
displayed, is there any way of getting the formula to look up the second or
third values, e.g. to look at a trend over a particular week. I have been
able to do this before somehow using these symbols {} but cannot remember
what I done!
Thanks for any suggestions,

Willie

I assume you have different lookup values. If they are numbers, e.g.,
5,3,6, you can array enter a hardcopied formula:

Either =VLOOKUP({5,3,6},B15,3,0) for a horizontal result or

=VLOOKUP({5;3;6},B15,3,0) for a vertical result.

If you mean multiple occurrences of the same lookup value,Jason Morin
has offered a solution (which I haven't tested). You might also consider
the Vlookups function in the freely downloadable file at
http://home.pacbell.net/beban for such a case.

Alan Beban

 

[Willie McL]

Brilliant guys, thanks a lot!!!

I assume you have different lookup values. If they are numbers, e.g.,
5,3,6, you can array enter a hardcopied formula:

Either =VLOOKUP({5,3,6},B15,3,0) for a horizontal result or

=VLOOKUP({5;3;6},B15,3,0) for a vertical result.

If you mean multiple occurrences of the same lookup value,Jason Morin
has offered a solution (which I haven't tested). You might also consider
the Vlookups function in the freely downloadable file at
http://home.pacbell.net/beban for such a case.

Alan Beban