|
Vlookup/match/offset over multiple columns of lable
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 |
| All times are GMT +1. The time now is 03:54 PM. |
Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com