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