VLookup function to right!

[blommerse]

I have a sheet with 5 colums,

A B C D 1
B C D E 2
C D E F 3

What I want is, using the vlookup function to right!
So the numbers are the lookup value

=VLOOKUP(B13,B3:G8,-2,FALSE)

If I use this, I get an error.

Who can help me???

Greets

 

[Ardus Petus]

=INDEX(B3:B8,MATCH(B13,D38,0))

HTH

 

[Arvi Laanemets]

Hi

1. VLOOKUP counts columns relatively to range passed to it. I.e. when you
pass the range B3:G8, then the first column is B, the second is C, etc. Any
column references outside passed range are meaningless for VLOOKUP. NB! 3rd
parameter in VLOOKUP really indicates column number in passed range, not
it's relative position to some other column.
2. The 1st column in range passed to VLOOKUP is ALWAYS the one, where the
condition value is looked for.

In your situation, either redesign your sheet, or use INDEX and MATCH
instead, something like

=INDEX($A$3:$A$8,MATCH($B$13,$B$3:$B$8,0),0)

 

[blommerse]

Thank you both!!

Greets

 

[blommerse]

Thank you both!!

Greets

 

[blommerse]

Thank you both!!

Greets

 

[hile trotman]

AP,
I tried the formula you suggested in this post, but it's not working for me.
I have the following values:
C D E
"Rptg
Dist" Dist District Name

6218 224 WEST CHICAGO
6218 224 WEST CHICAGO
6218 224 WEST CHICAGO
6218 224 WEST CHICAGO
6218 6218 ROMEOVILLE
6218 6218 ROMEOVILLE
6218 6218 ROMEOVILLE

I need to look up the value under "reporting dist" which will always be the
same value for the whole list, in the "dist" column (which will have several
"unsorted" values), when it finds a match, I want it to return the name of
that district which will be under "Dist Name" column. The column headings
start on row7 and row 8 is left blank for using the autofilter command, data
starts on row 9 and the length of the list varies. So the answer for this ex
should be "Romeoville" in cell H3

I have to post this today and this is the only thing keeping me from
finishing this sheet. Please help! Thanks!

 

[Richard Buttrey]

AP,
I tried the formula you suggested in this post, but it's not working for me.
I have the following values:
C D E
"Rptg
Dist" Dist District Name

6218 224 WEST CHICAGO
6218 224 WEST CHICAGO
6218 224 WEST CHICAGO
6218 224 WEST CHICAGO
6218 6218 ROMEOVILLE
6218 6218 ROMEOVILLE
6218 6218 ROMEOVILLE

I need to look up the value under "reporting dist" which will always be the
same value for the whole list, in the "dist" column (which will have several
"unsorted" values), when it finds a match, I want it to return the name of
that district which will be under "Dist Name" column. The column headings
start on row7 and row 8 is left blank for using the autofilter command, data
starts on row 9 and the length of the list varies. So the answer for this ex
should be "Romeoville" in cell H3

I have to post this today and this is the only thing keeping me from
finishing this sheet. Please help! Thanks!

=VLOOKUP($A$9,$B$9:$C$100,2,FALSE)

HTH


__
Richard Buttrey
Grappenhall, Cheshire, UK
__________________________

 

[hile trotman]

Thanks Richard but this won't work either, I can't specify an array with cell
numbers because the number of rows will change from report to report and it
can be in the thousands and i don't know where the reporting dist # will be
in column d, the array will have to be $D:$E. But it doesn't work. It's
picking up the first name in column E. I had false as a parameter which means
the list does not need to be sorted but i don't think it wants to work. I've
seen other ways in this forum by using INDEX and MATCH, but I don't
understand the posts or how these 2 functions work together so I haven't been
able to use that either.

Any other help you can provide will be appreciated. Thanks!

 

[Richard Buttrey]

Thanks Richard but this won't work either, I can't specify an array with cell
numbers because the number of rows will change from report to report and it
can be in the thousands and i don't know where the reporting dist # will be
in column d, the array will have to be $D:$E. But it doesn't work. It's
picking up the first name in column E. I had false as a parameter which means
the list does not need to be sorted but i don't think it wants to work. I've
seen other ways in this forum by using INDEX and MATCH, but I don't
understand the posts or how these 2 functions work together so I haven't been
able to use that either.

Any other help you can provide will be appreciated. Thanks!

Hi, I only specified up to row 100 as an example. If you can't be
certain of the number of rows, just change the last reference to
65536, i.e.

=VLOOKUP($A$9,$D$9:$E$65536,2,FALSE)

HTH
__
Richard Buttrey
Grappenhall, Cheshire, UK
__________________________