Vlookup/match/offset over multiple columns of lable

[csw78]

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

 

[swatsp0p]

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

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

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

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

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

Does this work for you

 

[csw78]

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

 

[swatsp0p]

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

Cheers!

Bruce

 

[Domenic]

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 o
columns, you can add to your IF statement. Alternatively, assumin
that A1:R4 contains six 3-column tables...

B10, copied to C10:

=VLOOKUP($A10,OFFSET($A$1:$R$4,0,MATCH(TRUE,COUNTIF(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, i
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 numbe
of rows for your tables increase, change the reference accordingly.
So, for example, if instead of 4 rows you have 10 rows, change thi
part *3-3,4,3) to *3-3,10,3). The 10 (in red) refers to the number o
rows, and the 3 (in blue) refers to how many columns each tabl
contains.

Hope this hleps!

 

[Domenic]

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

 

[Domenic]

The first formula can be changed to eliminate the need to confirm wit
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!