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 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,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 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?

[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 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,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, 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!

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]

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

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,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, 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]

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!

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,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, 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!