Hi. I would like to perform vlookup over several columns.

f
a 1 5 e 9 13
b 2 6 f 10 14
c 3 7 g 11 15
d 4 8 h 12 16

My intent is to lookup A1="f",
over the table of B2:G5
to obtain the offset values associated with "f", namely 10, 14,...

The simplest way is to move "e through h" below "d" and perform a
regular vlookup, but I prefer not doing so because they have different
properties and I intent to expand each column in the future.

Thanks for the headsup.

Regards,
csw


--
csw78
------------------------------------------------------------------------
csw78's Profile: http://www.excelforum.com/member.php...o&userid=23008
View this thread: http://www.excelforum.com/showthread...hreadid=376745

Because of the way VLOOKUP works, it has to search the first column of
the given range. In your case, I would name my two sets of columns as
TableL (range B2:D5) and TableR (range E2:G5). Then use ISERROR to see
if my lookup value is contained in each table... such as:

=IF(ISERROR(VLOOKUP(A1,TableL,2,0)),VLOOKUP(A1,Tab leR,2)&",
"&VLOOKUP(A1,TableR,3),VLOOKUP(A1,TableL,2)&", "&VLOOKUP(A1,TableL,3))

in your example, checking table L for "f" would return an error
(because it is not found in that table), it would then look in table R
(ISERROR=TRUE) and return the values from columns 2 and 3 of that range
(in your case 10,14).

notice the use of concatenation to draw both results into one cell per
your example (namely 10, 14)

If A1 contains 'b', (ISERROR= FALSE) the formula would return "2, 6"

Does this work for you?


--
swatsp0p


------------------------------------------------------------------------
swatsp0p's Profile: http://www.excelforum.com/member.php...o&userid=15101
View this thread: http://www.excelforum.com/showthread...hreadid=376745

Hi, it works great. I never thought about breaking them into smaller
tables and check with iserror. I was thinking more like matching,
countif or offset. I guess I was in the left field all these times.
Thanks again, Bruce.

Regards,
csw


--
csw78
------------------------------------------------------------------------
csw78's Profile: http://www.excelforum.com/member.php...o&userid=23008
View this thread: http://www.excelforum.com/showthread...hreadid=376745

I am glad this worked for you. Thanks for the feedback, it is always
appreciated.

Cheers!

Bruce


--
swatsp0p


------------------------------------------------------------------------
swatsp0p's Profile: http://www.excelforum.com/member.php...o&userid=15101
View this thread: http://www.excelforum.com/showthread...hreadid=376745

Here's another way...

Assuming that A1:F4 contains two 3-column tables...

B10, copied to C10:

=VLOOKUP($A10,IF($A$1:$A$4=$A10,$A$1:$C$4,$D$1:$F$ 4),COLUMNS($B$10:B10)+1,0)

...where A10 contains your lookup value. If you have a number of
columns, you can add to your IF statement. Alternatively, assuming
that A1:R4 contains six 3-column tables...

B10, copied to C10:

=VLOOKUP($A10,OFFSET($A$1:$R$4,0,MATCH(TRUE,COUNTI F(OFFSET($A$1:$R$4,0,{0,3,6,9,12,15},4,1),$A10)0, 0)*3-3,4,3),COLUMNS($B$10:B10)+1,0)

...confirmed with CONTROL+SHIFT+ENTER, not just ENTER.

Adjust the range and array constant accordingly. So, for example, if
A1:AA4 contains nine 3-column tables, change $A$1:$R$4 to $A$1:$AA$4,
and {0,3,6,9,12,15} to {0,3,6,9,12,15,18,21,24}. Also, if the number
of rows for your tables increase, change the reference accordingly.
So, for example, if instead of 4 rows you have 10 rows, change this
part *3-3,4,3) to *3-3,10,3). The 10 (in red) refers to the number of
rows, and the 3 (in blue) refers to how many columns each table
contains.

Hope this hleps!

csw78 Wrote:
Hi. I would like to perform vlookup over several columns.

f
a 1 5 e 9 13
b 2 6 f 10 14
c 3 7 g 11 15
d 4 8 h 12 16

My intent is to lookup A1="f",
over the table of B2:G5
to obtain the offset values associated with "f", namely 10, 14,...

The simplest way is to move "e through h" below "d" and perform a
regular vlookup, but I prefer not doing so because they have different
properties and I intent to expand each column in the future.

Thanks for the headsup.

Regards,
csw


--
Domenic
------------------------------------------------------------------------
Domenic's Profile: http://www.excelforum.com/member.php...o&userid=10785
View this thread: http://www.excelforum.com/showthread...hreadid=376745

I forgot to mention that the first formula also needs to be confirmed
with CONTROL+SHIFT+ENTER.

Domenic Wrote:
Here's another way...

Assuming that A1:F4 contains two 3-column tables...

B10, copied to C10:

=VLOOKUP($A10,IF($A$1:$A$4=$A10,$A$1:$C$4,$D$1:$F$ 4),COLUMNS($B$10:B10)+1,0)

...where A10 contains your lookup value. If you have a number of
columns, you can add to your IF statement. Alternatively, assuming
that A1:R4 contains six 3-column tables...

B10, copied to C10:

=VLOOKUP($A10,OFFSET($A$1:$R$4,0,MATCH(TRUE,COUNTI F(OFFSET($A$1:$R$4,0,{0,3,6,9,12,15},4,1),$A10)0, 0)*3-3,4,3),COLUMNS($B$10:B10)+1,0)

...confirmed with CONTROL+SHIFT+ENTER, not just ENTER.

Adjust the range and array constant accordingly. So, for example, if
A1:AA4 contains nine 3-column tables, change $A$1:$R$4 to $A$1:$AA$4,
and {0,3,6,9,12,15} to {0,3,6,9,12,15,18,21,24}. Also, if the number
of rows for your tables increase, change the reference accordingly.
So, for example, if instead of 4 rows you have 10 rows, change this
part *3-3,4,3) to *3-3,10,3). The 10 (in red) refers to the number of
rows, and the 3 (in blue) refers to how many columns each table
contains.

Hope this hleps!


--
Domenic
------------------------------------------------------------------------
Domenic's Profile: http://www.excelforum.com/member.php...o&userid=10785
View this thread: http://www.excelforum.com/showthread...hreadid=376745

The first formula can be changed to eliminate the need to confirm with
CONTROL+SHIFT+ENTER...

B10, copied to C10:

=VLOOKUP($A10,IF(ISNUMBER(MATCH($A10,$A$1:$A$4,0)) ,$A$1:$C$4,$D$1:$F$4),COLUMNS($B$10:B10)+1,0)

...confirmed with just ENTER.

Hope this helps!

Domenic Wrote:
Here's another way...

Assuming that A1:F4 contains two 3-column tables...

B10, copied to C10:

=VLOOKUP($A10,IF($A$1:$A$4=$A10,$A$1:$C$4,$D$1:$F$ 4),COLUMNS($B$10:B10)+1,0)

...where A10 contains your lookup value. If you have a number of
columns, you can add to your IF statement. Alternatively, assuming
that A1:R4 contains six 3-column tables...

B10, copied to C10:

=VLOOKUP($A10,OFFSET($A$1:$R$4,0,MATCH(TRUE,COUNTI F(OFFSET($A$1:$R$4,0,{0,3,6,9,12,15},4,1),$A10)0, 0)*3-3,4,3),COLUMNS($B$10:B10)+1,0)

...confirmed with CONTROL+SHIFT+ENTER, not just ENTER.

Adjust the range and array constant accordingly. So, for example, if
A1:AA4 contains nine 3-column tables, change $A$1:$R$4 to $A$1:$AA$4,
and {0,3,6,9,12,15} to {0,3,6,9,12,15,18,21,24}. Also, if the number
of rows for your tables increase, change the reference accordingly.
So, for example, if instead of 4 rows you have 10 rows, change this
part *3-3,4,3) to *3-3,10,3). The 10 (in red) refers to the number of
rows, and the 3 (in blue) refers to how many columns each table
contains.

Hope this hleps!


--
Domenic
------------------------------------------------------------------------
Domenic's Profile: http://www.excelforum.com/member.php...o&userid=10785
View this thread: http://www.excelforum.com/showthread...hreadid=376745