Vlookup Help 


Vlookup Help  Excel 
View Answers 
Hi,
I have a vlookup where the table_array is the variable part..How do i do it?
For example.
VLOOKUP(B1, XXX, 3,FALSE)
So in the formula XXX is the variable part..but i am not able to do it..Can anyone help in this regard...Basically, XXX is either Table1 or Table2 or Table3 etc..but i am not able to figure this out..
Please help.
Satish
I have a vlookup where the table_array is the variable part..How do i do it?
For example.
VLOOKUP(B1, XXX, 3,FALSE)
So in the formula XXX is the variable part..but i am not able to do it..Can anyone help in this regard...Basically, XXX is either Table1 or Table2 or Table3 etc..but i am not able to figure this out..
Please help.
Satish
Similar Excel Video Tutorials
VLOOKUP 11 Unusual Examples
 See these 11 VLOOKUP tricks:
1.VLOOKUP algorithm
2.VLOOKUP, Named Ranges, Exact Match, COLUMNS function& Data Validation List
3.Com ...
1.VLOOKUP algorithm
2.VLOOKUP, Named Ranges, Exact Match, COLUMNS function& Data Validation List
3.Com ...
VLOOKUP Categories
 See when to use IF and when to use VLOOKUP depending on your category setup. See when the VLOOKUP function beats the IF function, but only if categori ...
VLOOKUP function formula 7 Examples
 Learn about:
1)VLOOKUP to lookup an exact match
2)VLOOKUP to lookup an approximate match
3)VLOOKUP can return a value to a cell
4) ...
1)VLOOKUP to lookup an exact match
2)VLOOKUP to lookup an approximate match
3)VLOOKUP can return a value to a cell
4) ...
IF AND vs. VLOOKUP
 See how to use the IF, AND and VLOOKUP functions to deliver the words Bid, No Bid, or Alert to a cell. Is VLOOKUP better than IF AND? ...
Helpful Excel Macros
Vlookup Function That Searches The Entire Workbook  UDF
 This UDF is a Vlookup function that searches the entire workbook in Excel. The syntax and usage of the function is exac
 This UDF is a Vlookup function that searches the entire workbook in Excel. The syntax and usage of the function is exac
Vlookup Macro to Return All Matching Results and Stack them with Previous Results
 This is very similar to the other Vlookup type Macro in that it returns all of the results that match a particular se
 This is very similar to the other Vlookup type Macro in that it returns all of the results that match a particular se
Vlookup Macro to Return All Matching Results from a Sheet in Excel
 This Excel Macro works like a better Vlookup function because it returns ALL of the matching results. Run the
 This Excel Macro works like a better Vlookup function because it returns ALL of the matching results. Run the
Determine if a Cell Contains a Function in Excel  Great for Conditional Formatting and Validation  UDF
 Determine if a cell in Excel contains a formula or function with this UDF (user defined function). This function return
 Determine if a cell in Excel contains a formula or function with this UDF (user defined function). This function return
Print Preview Display for Specific Worksheets in Excel
 This free Excel macro allows you to display the print preview mode or window in Excel for a specified Excel worksheet.
 This free Excel macro allows you to display the print preview mode or window in Excel for a specified Excel worksheet.
Similar Topics
Hey Guys I Have I Q
I'd Like To Make To Make Every Thing Variable In "Gray Table" So I Can Choose According To My Need I Work
Ex
1 Ranges Variables So I Can Choose The Range I Need
2 Look Up Table Variable So I Can Choose Between Tables I need
3 Color Every Range With Specific Color Through Drop Down List Color
Sheet1
A B C D E F G H I J K L M N O P Q 1 A1 C 700 Range Tables Color Table1 Table2 Table3 2 A2 C 701 First LookUp A1 A4 Table1 Blue Sky A1 C 700 A1 Z 500 A1 Y 300 3 A3 C 702 Second LookUp A5 A13 Table3 Red A2 C 701 A2 Z 501 A2 Y 301 4 A4 C 703 Third LookUp A14 A18 Table2 Yellow A3 C 702 A3 Z 502 A3 Y 302 5 A5 Y 304 A4 C 703 A4 Z 503 A4 Y 303 6 A6 Y 305 A5 C 704 A5 Z 504 A5 Y 304 7 A7 Y 306 A6 C 705 A6 Z 505 A6 Y 305 8 A8 Y 307 A7 C 706 A7 Z 506 A7 Y 306 9 A9 Y 308 A8 C 707 A8 Z 507 A8 Y 307 10 A10 Y 309 A9 C 708 A9 Z 508 A9 Y 308 11 A11 Y 310 A10 C 709 A10 Z 509 A10 Y 309 12 A12 Y 311 A11 C 710 A11 Z 510 A11 Y 310 13 A13 Y 312 A12 C 711 A12 Z 511 A12 Y 311 14 A14 Z 513 A13 C 712 A13 Z 512 A13 Y 312 15 A15 Z 514 A14 C 713 A14 Z 513 A14 Y 313 16 A16 Z 515 A15 C 714 A15 Z 514 A15 Y 314 17 A17 Z 516 A16 C 715 A16 Z 515 A16 Y 315 18 A18 Z 517 A17 C 716 A17 Z 516 A17 Y 316 19 A18 C 717 A18 Z 517 A18 Y 317 20 A19 C 718 A19 Z 518 A19 Y 318 21 A20 C 719 A20 Z519 A20 Y 319
Spreadsheet Formulas Cell Formula B1 =VLOOKUP(A1,Table1,2,0) B2 =VLOOKUP(A2,Table1,2,0) B3 =VLOOKUP(A3,Table1,2,0) B4 =VLOOKUP(A4,Table1,2,0) B5 =VLOOKUP(A5,Table3,2,0) B6 =VLOOKUP(A6,Table3,2,0) B7 =VLOOKUP(A7,Table3,2,0) B8 =VLOOKUP(A8,Table3,2,0) B9 =VLOOKUP(A9,Table3,2,0) B10 =VLOOKUP(A10,Table3,2,0) B11 =VLOOKUP(A11,Table3,2,0) B12 =VLOOKUP(A12,Table3,2,0) B13 =VLOOKUP(A13,Table3,2,0) B14 =VLOOKUP(A14,Table2,2,0) B15 =VLOOKUP(A15,Table2,2,0) B16 =VLOOKUP(A16,Table2,2,0) B17 =VLOOKUP(A17,Table2,2,0) B18 =VLOOKUP(A18,Table2,2,0)
Excel tables to the web >> Excel Jeanie HTML 4
I'd Like To Make To Make Every Thing Variable In "Gray Table" So I Can Choose According To My Need I Work
Ex
1 Ranges Variables So I Can Choose The Range I Need
2 Look Up Table Variable So I Can Choose Between Tables I need
3 Color Every Range With Specific Color Through Drop Down List Color
Sheet1
A B C D E F G H I J K L M N O P Q 1 A1 C 700 Range Tables Color Table1 Table2 Table3 2 A2 C 701 First LookUp A1 A4 Table1 Blue Sky A1 C 700 A1 Z 500 A1 Y 300 3 A3 C 702 Second LookUp A5 A13 Table3 Red A2 C 701 A2 Z 501 A2 Y 301 4 A4 C 703 Third LookUp A14 A18 Table2 Yellow A3 C 702 A3 Z 502 A3 Y 302 5 A5 Y 304 A4 C 703 A4 Z 503 A4 Y 303 6 A6 Y 305 A5 C 704 A5 Z 504 A5 Y 304 7 A7 Y 306 A6 C 705 A6 Z 505 A6 Y 305 8 A8 Y 307 A7 C 706 A7 Z 506 A7 Y 306 9 A9 Y 308 A8 C 707 A8 Z 507 A8 Y 307 10 A10 Y 309 A9 C 708 A9 Z 508 A9 Y 308 11 A11 Y 310 A10 C 709 A10 Z 509 A10 Y 309 12 A12 Y 311 A11 C 710 A11 Z 510 A11 Y 310 13 A13 Y 312 A12 C 711 A12 Z 511 A12 Y 311 14 A14 Z 513 A13 C 712 A13 Z 512 A13 Y 312 15 A15 Z 514 A14 C 713 A14 Z 513 A14 Y 313 16 A16 Z 515 A15 C 714 A15 Z 514 A15 Y 314 17 A17 Z 516 A16 C 715 A16 Z 515 A16 Y 315 18 A18 Z 517 A17 C 716 A17 Z 516 A17 Y 316 19 A18 C 717 A18 Z 517 A18 Y 317 20 A19 C 718 A19 Z 518 A19 Y 318 21 A20 C 719 A20 Z519 A20 Y 319
Spreadsheet Formulas Cell Formula B1 =VLOOKUP(A1,Table1,2,0) B2 =VLOOKUP(A2,Table1,2,0) B3 =VLOOKUP(A3,Table1,2,0) B4 =VLOOKUP(A4,Table1,2,0) B5 =VLOOKUP(A5,Table3,2,0) B6 =VLOOKUP(A6,Table3,2,0) B7 =VLOOKUP(A7,Table3,2,0) B8 =VLOOKUP(A8,Table3,2,0) B9 =VLOOKUP(A9,Table3,2,0) B10 =VLOOKUP(A10,Table3,2,0) B11 =VLOOKUP(A11,Table3,2,0) B12 =VLOOKUP(A12,Table3,2,0) B13 =VLOOKUP(A13,Table3,2,0) B14 =VLOOKUP(A14,Table2,2,0) B15 =VLOOKUP(A15,Table2,2,0) B16 =VLOOKUP(A16,Table2,2,0) B17 =VLOOKUP(A17,Table2,2,0) B18 =VLOOKUP(A18,Table2,2,0)
Excel tables to the web >> Excel Jeanie HTML 4
I've got four tables with negative and positive values and want to determine the largest magnitude of a subset of four values within those four tables. In other words if the largest magnitude is negative I want the negative value of the four subsets or viseversa.
I've tried the following but got an error stating it was too long:
= IF(ABS(VLOOKUP(table1)) = MAX(ABS(VLOOKUP(table1)), ABS(VLOOKUP(table2)), ABS(VLOOKUP(table3)), ABS(VLOOKUP(table4))), VLOOKUP(table1), IF(ABS(VLOOKUP(table2)) = MAX(ABS(VLOOKUP(table1)), ABS(VLOOKUP(table2)), ABS(VLOOKUP(table3)), ABS(VLOOKUP(table4))), VLOOKUP(table2), IF(ABS(VLOOKUP(table3)) = MAX(ABS(VLOOKUP(table1)), ABS(VLOOKUP(table2)), ABS(VLOOKUP(table3)), ABS(VLOOKUP(table4))), VLOOKUP(table3), VLOOKUP(table4))))
The repeating line needs attention in my mind:
MAX(ABS(VLOOKUP(table1)), ABS(VLOOKUP(table2)), ABS(VLOOKUP(table3)), ABS(VLOOKUP(table4)))
Thanks
I've tried the following but got an error stating it was too long:
= IF(ABS(VLOOKUP(table1)) = MAX(ABS(VLOOKUP(table1)), ABS(VLOOKUP(table2)), ABS(VLOOKUP(table3)), ABS(VLOOKUP(table4))), VLOOKUP(table1), IF(ABS(VLOOKUP(table2)) = MAX(ABS(VLOOKUP(table1)), ABS(VLOOKUP(table2)), ABS(VLOOKUP(table3)), ABS(VLOOKUP(table4))), VLOOKUP(table2), IF(ABS(VLOOKUP(table3)) = MAX(ABS(VLOOKUP(table1)), ABS(VLOOKUP(table2)), ABS(VLOOKUP(table3)), ABS(VLOOKUP(table4))), VLOOKUP(table3), VLOOKUP(table4))))
The repeating line needs attention in my mind:
MAX(ABS(VLOOKUP(table1)), ABS(VLOOKUP(table2)), ABS(VLOOKUP(table3)), ABS(VLOOKUP(table4)))
Thanks
I've got four tables with negative and positive values and want to determine the largest magnitude of a subset of four values within those four tables. In other words if the largest magnitude is negative I want the negative value of the four subsets or viseversa.
I've tried the following but got an error stating it was too long:
= IF(ABS(VLOOKUP(table1)) = MAX(ABS(VLOOKUP(table1)), ABS(VLOOKUP(table2)), ABS(VLOOKUP(table3)), ABS(VLOOKUP(table4))), VLOOKUP(table1), IF(ABS(VLOOKUP(table2)) = MAX(ABS(VLOOKUP(table1)), ABS(VLOOKUP(table2)), ABS(VLOOKUP(table3)), ABS(VLOOKUP(table4))), VLOOKUP(table2), IF(ABS(VLOOKUP(table3)) = MAX(ABS(VLOOKUP(table1)), ABS(VLOOKUP(table2)), ABS(VLOOKUP(table3)), ABS(VLOOKUP(table4))), VLOOKUP(table3), VLOOKUP(table4))))
The repeating line needs attention in my mind:
MAX(ABS(VLOOKUP(table1)), ABS(VLOOKUP(table2)), ABS(VLOOKUP(table3)), ABS(VLOOKUP(table4)))
Thanks
I've tried the following but got an error stating it was too long:
= IF(ABS(VLOOKUP(table1)) = MAX(ABS(VLOOKUP(table1)), ABS(VLOOKUP(table2)), ABS(VLOOKUP(table3)), ABS(VLOOKUP(table4))), VLOOKUP(table1), IF(ABS(VLOOKUP(table2)) = MAX(ABS(VLOOKUP(table1)), ABS(VLOOKUP(table2)), ABS(VLOOKUP(table3)), ABS(VLOOKUP(table4))), VLOOKUP(table2), IF(ABS(VLOOKUP(table3)) = MAX(ABS(VLOOKUP(table1)), ABS(VLOOKUP(table2)), ABS(VLOOKUP(table3)), ABS(VLOOKUP(table4))), VLOOKUP(table3), VLOOKUP(table4))))
The repeating line needs attention in my mind:
MAX(ABS(VLOOKUP(table1)), ABS(VLOOKUP(table2)), ABS(VLOOKUP(table3)), ABS(VLOOKUP(table4)))
Thanks
I've got a worksheet with several tables, each of which I've assigned a defined name. At the top of my worksheet I have several cells containing lookup functions, and these are repeated for each defined table on the sheet.
Right now I'm creating all my lookup functions for a single table, copying the cells containing these functions, and editing the references to the defined names in the copies by hand, giving me a final product something like this:
Code:
To get this result, I'd normally do all the formulas in Column A first, then copy these formulas to Columns BD, but doing that gives me the result below:
Code:
As you can see, I still have to go in to Columns BD and manually correct the table reference names. So, my question is this: is it possible to enter the text values Table1, Table2, etc. in Row 1, and somehow have all my vlookup functions extract the name of the table to use from these cells? I know I can't just do something like having the formula in A3 =VLOOKUP(A$2,A$1,2) since that will make the formula think that the range A1 itself is where I want to do the lookup. Any ideas?
Right now I'm creating all my lookup functions for a single table, copying the cells containing these functions, and editing the references to the defined names in the copies by hand, giving me a final product something like this:
Code:
A B C D 1 2 10 10 10 10 3 =VLOOKUP(A$2,Table1,2) =VLOOKUP(B$2,Table2,2) =VLOOKUP(C$2,Table3,2) =VLOOKUP(D$2,Table4,2) 4 =VLOOKUP(A$2,Table1,3) =VLOOKUP(B$2,Table2,3) =VLOOKUP(C$2,Table3,3) =VLOOKUP(D$2,Table4,3) 5 =VLOOKUP(A$2,Table1,4) =VLOOKUP(B$2,Table2,4) =VLOOKUP(C$2,Table3,4) =VLOOKUP(D$2,Table4,4) 6 =VLOOKUP(A$2,Table1,5) =VLOOKUP(B$2,Table2,5) =VLOOKUP(C$2,Table3,5) =VLOOKUP(D$2,Table4,5)
To get this result, I'd normally do all the formulas in Column A first, then copy these formulas to Columns BD, but doing that gives me the result below:
Code:
A B C D A B C D 1 2 10 10 10 10 3 =VLOOKUP(A$2,Table1,2) =VLOOKUP(B$2,Table1,2) =VLOOKUP(C$2,Table1,2) =VLOOKUP(D$2,Table1,2) 4 =VLOOKUP(A$2,Table1,3) =VLOOKUP(B$2,Table1,3) =VLOOKUP(C$2,Table1,3) =VLOOKUP(D$2,Table1,3) 5 =VLOOKUP(A$2,Table1,4) =VLOOKUP(B$2,Table1,4) =VLOOKUP(C$2,Table1,4) =VLOOKUP(D$2,Table1,4) 6 =VLOOKUP(A$2,Table1,5) =VLOOKUP(B$2,Table1,5) =VLOOKUP(C$2,Table1,5) =VLOOKUP(D$2,Table1,5)
As you can see, I still have to go in to Columns BD and manually correct the table reference names. So, my question is this: is it possible to enter the text values Table1, Table2, etc. in Row 1, and somehow have all my vlookup functions extract the name of the table to use from these cells? I know I can't just do something like having the formula in A3 =VLOOKUP(A$2,A$1,2) since that will make the formula think that the range A1 itself is where I want to do the lookup. Any ideas?
Here is my attempt.
=IF(A1="table1",VLOOKUP(B4,Matrix.xls!table1,2,1)*B6),IF(A1="table2",VLOOKUP(B4,Matrix.xls!table2,2,1)*B6),IF(A1="table3",VLOOKUP(B4,Matrix.xls!table3,2,1)*B6),IF(A1="table4",VLOOKUP(B4,Matrix.xls!table4,2,1)*B6),""))))
I have a validation in cell A1 with a dropdown list, table1,table2,table3,table4.
I want to be able to select that and then pull data from the matrix sheet based on the ranges with the same name using a vlookup returning the 2nd column.
Is this possible using this formula? Think I may be barking up the wrong tree here.
Thank you
=IF(A1="table1",VLOOKUP(B4,Matrix.xls!table1,2,1)*B6),IF(A1="table2",VLOOKUP(B4,Matrix.xls!table2,2,1)*B6),IF(A1="table3",VLOOKUP(B4,Matrix.xls!table3,2,1)*B6),IF(A1="table4",VLOOKUP(B4,Matrix.xls!table4,2,1)*B6),""))))
I have a validation in cell A1 with a dropdown list, table1,table2,table3,table4.
I want to be able to select that and then pull data from the matrix sheet based on the ranges with the same name using a vlookup returning the 2nd column.
Is this possible using this formula? Think I may be barking up the wrong tree here.
Thank you
I would like to code cell F3 so that if A3 & A4 are the same and D3 and D4 are the same then F3 is divided by 2 and F4= F3. If A3 is different from A4 and/or D3 is different from D4 then I do not want anything to happen to the value in F3
So using the example below if F3 were to entered as 58 being that A3=A4, D3 = D4 are all the same, F3 would be divided by 2 (equaling 29) and then F4 would equal the value of F3. Any suggestions???
* A B C D E F G H I J K L M N O P Q R 3 9Jun 2 160 Jumbo TC * * * 2 0 50.9 0 2.5 0 0 3.5 x 42 * 0 PCS 4 9Jun 3 160 Jumbo Alpina * * * 2 0 50.9 0 2.5 0 0 3.5 x 150 * 0 FT
Spreadsheet Formulas Cell Formula C3 =A3DATE(YEAR (A3) ,1,0) D3 =VLOOKUP(B3,Table1,2,FALSE) E3 =VLOOKUP(B3,Table1,3,FALSE) I3 =VLOOKUP(B3,Table1,5,FALSE) J3 =F3*VLOOKUP(B3,Table1,7,FALSE) K3 =VLOOKUP(B3,Table1,7,FALSE) L3 =F3*VLOOKUP(B3,Table1,9,FALSE) M3 =VLOOKUP(B3,Table1,9,FALSE) N3 =F3*VLOOKUP(B3,Table1,11,FALSE) O3 =F3*VLOOKUP(B3,Table1,12,FALSE) P3 =VLOOKUP(B3,Table1,13,FALSE) R3 =Q3*VLOOKUP(B3,Table1,14,FALSE)& " " & VLOOKUP(B3,Table1,15,FALSE) C4 =A4DATE(YEAR (A4) ,1,0) D4 =VLOOKUP(B4,Table1,2,FALSE) E4 =VLOOKUP(B4,Table1,3,FALSE) I4 =VLOOKUP(B4,Table1,5,FALSE) J4 =F4*VLOOKUP(B4,Table1,7,FALSE) K4 =VLOOKUP(B4,Table1,7,FALSE) L4 =F4*VLOOKUP(B4,Table1,9,FALSE) M4 =VLOOKUP(B4,Table1,9,FALSE) N4 =F4*VLOOKUP(B4,Table1,11,FALSE) O4 =F4*VLOOKUP(B4,Table1,12,FALSE) P4 =VLOOKUP(B4,Table1,13,FALSE) R4 =Q4*VLOOKUP(B4,Table1,14,FALSE)& " " & VLOOKUP(B4,Table1,15,FALSE)
Excel tables to the web >> Excel Jeanie HTML 4
So using the example below if F3 were to entered as 58 being that A3=A4, D3 = D4 are all the same, F3 would be divided by 2 (equaling 29) and then F4 would equal the value of F3. Any suggestions???
* A B C D E F G H I J K L M N O P Q R 3 9Jun 2 160 Jumbo TC * * * 2 0 50.9 0 2.5 0 0 3.5 x 42 * 0 PCS 4 9Jun 3 160 Jumbo Alpina * * * 2 0 50.9 0 2.5 0 0 3.5 x 150 * 0 FT
Spreadsheet Formulas Cell Formula C3 =A3DATE(YEAR (A3) ,1,0) D3 =VLOOKUP(B3,Table1,2,FALSE) E3 =VLOOKUP(B3,Table1,3,FALSE) I3 =VLOOKUP(B3,Table1,5,FALSE) J3 =F3*VLOOKUP(B3,Table1,7,FALSE) K3 =VLOOKUP(B3,Table1,7,FALSE) L3 =F3*VLOOKUP(B3,Table1,9,FALSE) M3 =VLOOKUP(B3,Table1,9,FALSE) N3 =F3*VLOOKUP(B3,Table1,11,FALSE) O3 =F3*VLOOKUP(B3,Table1,12,FALSE) P3 =VLOOKUP(B3,Table1,13,FALSE) R3 =Q3*VLOOKUP(B3,Table1,14,FALSE)& " " & VLOOKUP(B3,Table1,15,FALSE) C4 =A4DATE(YEAR (A4) ,1,0) D4 =VLOOKUP(B4,Table1,2,FALSE) E4 =VLOOKUP(B4,Table1,3,FALSE) I4 =VLOOKUP(B4,Table1,5,FALSE) J4 =F4*VLOOKUP(B4,Table1,7,FALSE) K4 =VLOOKUP(B4,Table1,7,FALSE) L4 =F4*VLOOKUP(B4,Table1,9,FALSE) M4 =VLOOKUP(B4,Table1,9,FALSE) N4 =F4*VLOOKUP(B4,Table1,11,FALSE) O4 =F4*VLOOKUP(B4,Table1,12,FALSE) P4 =VLOOKUP(B4,Table1,13,FALSE) R4 =Q4*VLOOKUP(B4,Table1,14,FALSE)& " " & VLOOKUP(B4,Table1,15,FALSE)
Excel tables to the web >> Excel Jeanie HTML 4
How do I write this as one formula.
if(C1=1,(Vlookup(D8,Table1,2,False),"") OR
if(C1=2,(Vlookup(D8,Table2,2,False),"") OR
if(C1=3,(Vlookup(D8,Table3,2,False),"").
I have spent so much time trying to come up with correct syntax or formula with no success. Thank you very much.
if(C1=1,(Vlookup(D8,Table1,2,False),"") OR
if(C1=2,(Vlookup(D8,Table2,2,False),"") OR
if(C1=3,(Vlookup(D8,Table3,2,False),"").
I have spent so much time trying to come up with correct syntax or formula with no success. Thank you very much.
I need help with a code so that if the lookup in W returns any value other than zero, it leaves X blank. If the lookup in W returns 0 then X=0
* W X 1 Tight Net Tight Net Rolls 2 3.25 5 3 2.25 3 4 0 0 5 0 0
Spreadsheet Formulas Cell Formula W2 =IF(VLOOKUP (C2,Table1,20,FALSE) ,VLOOKUP (C2,Table1,20,FALSE) ,0) W3 =IF(VLOOKUP (C3,Table1,20,FALSE) ,VLOOKUP (C3,Table1,20,FALSE) ,0) W4 =IF(VLOOKUP (C4,Table1,20,FALSE) ,VLOOKUP (C4,Table1,20,FALSE) ,0) X4 =IF(Table2[[#This Row],[Tight Net ]]=0,0,ISBLANK) W5 =IF(VLOOKUP (C5,Table1,20,FALSE) ,VLOOKUP (C5,Table1,20,FALSE) ,0)
Excel tables to the web >> Excel Jeanie HTML 4
* W X 1 Tight Net Tight Net Rolls 2 3.25 5 3 2.25 3 4 0 0 5 0 0
Spreadsheet Formulas Cell Formula W2 =IF(VLOOKUP (C2,Table1,20,FALSE) ,VLOOKUP (C2,Table1,20,FALSE) ,0) W3 =IF(VLOOKUP (C3,Table1,20,FALSE) ,VLOOKUP (C3,Table1,20,FALSE) ,0) W4 =IF(VLOOKUP (C4,Table1,20,FALSE) ,VLOOKUP (C4,Table1,20,FALSE) ,0) X4 =IF(Table2[[#This Row],[Tight Net ]]=0,0,ISBLANK) W5 =IF(VLOOKUP (C5,Table1,20,FALSE) ,VLOOKUP (C5,Table1,20,FALSE) ,0)
Excel tables to the web >> Excel Jeanie HTML 4
Is there a way to use a variable for the table_array in a VLOOKUP, specifically, where the table_array is in another workbook? I'm not afraid to use a VBA function if necessary but it'd be nice to have the function all 'selfcontained' using standard formula commands.
This is an example of what I'm looking to do:
Code:
Since a string is returned, this function fails. I'm guessing I need a Range returned?
The "CONCATENATE("..." part is the piece causing me the issue. Depending upon what Form (radio) button the user clicks (that part is done), the table_array range will change.
Suggestions?
This is an example of what I'm looking to do:
Code:
=VLOOKUP(TEXT($A7,"###0"), CONCATENATE("SEASONALLY ADJUSTED.xls!MinMax",ClothingType), 22 + GenderOffset, FALSE)
Since a string is returned, this function fails. I'm guessing I need a Range returned?
The "CONCATENATE("..." part is the piece causing me the issue. Depending upon what Form (radio) button the user clicks (that part is done), the table_array range will change.
Suggestions?
Good day,
I hope that I am not asking too much with this one.
I have a workbook with 3 worksheets, 2 containing data and 1 used as a search and display sheet. I enter information into a cell, press enter and the relevant data is displayed in various cells.
I would like to create a userform to do the job of the search/display sheet, this is mainly to make it look more professional!
What do I need to add to the userform? Textboxes, Labels, Command Buttons etc, etc.
How would I go about transferring/copying the following data into a userform?
Thanks
Ak
A1 B1 C1 D1 E1 P1 =TODAY() =NOW() A3 =COUNT(Master!A4:A301) Enter Product code below P N M E F S W S S G R A5 =VLOOKUP(C5,Master,7,FALSE) =VLOOKUP(C5,Master,8,FALSE) =VLOOKUP(C5,Master,9,FALSE) =VLOOKUP(C5,Master,10,FALSE) =VLOOKUP(C5,Master,11,FALSE) =VLOOKUP(C5,Master,12,FALSE) =VLOOKUP(C5,Master,13,FALSE) =VLOOKUP(C5,Master,14,FALSE) =VLOOKUP(C5,Master,15,FALSE) =VLOOKUP(C5,Master,16,FALSE) =VLOOKUP(C5,Master,17,FALSE) B M O M C M G H L S P A7 Product details =VLOOKUP(C5,Master,18,FALSE) =VLOOKUP(C5,Master,19,FALSE) =VLOOKUP(C5,Master,20,FALSE) =VLOOKUP(C5,Master,21,FALSE) =VLOOKUP(C5,Master,22,FALSE) =VLOOKUP(C5,Master,23,FALSE) =VLOOKUP(C5,Master,24,FALSE) =VLOOKUP(C5,Master,25,FALSE) =VLOOKUP(C5,Master,26,FALSE) =VLOOKUP(C5,Master,27,FALSE) =VLOOKUP(C5,Master,28,FALSE) Des A9 =IF(ISNA(VLOOKUP(C5,Master,2,FALSE)),"Incorrect Product code",VLOOKUP(C5,Master,2,FALSE)) =VLOOKUP(C5,Master,29,FALSE) =VLOOKUP(C5,Master,30,FALSE) =VLOOKUP(C5,Master,31,FALSE) =VLOOKUP(C5,Master,32,FALSE) =VLOOKUP(C5,Master,33,FALSE) =VLOOKUP(C5,Master,34,FALSE) =VLOOKUP(C5,Master,35,FALSE) =VLOOKUP(C5,Master,36,FALSE) =VLOOKUP(C5,Master,37,FALSE) =VLOOKUP(C5,Master,38,FALSE) AL =VLOOKUP(C5,Master,39,FALSE) =VLOOKUP(C5,Master,40,FALSE) =VLOOKUP(C5,Master,41,FALSE) =VLOOKUP(C5,Master,42,FALSE) =VLOOKUP(C5,Master,43,FALSE) =VLOOKUP(C5,Master,44,FALSE) =VLOOKUP(C5,Master,45,FALSE) =VLOOKUP(C5,Master,46,FALSE) =VLOOKUP(C5,Master,47,FALSE) =VLOOKUP(C5,Master,48,FALSE) A11 =VLOOKUP(C5,Master,3,FALSE) =VLOOKUP(C5,Master,49,FALSE) =VLOOKUP(C5,Master,50,FALSE) =VLOOKUP(C5,Master,51,FALSE) =VLOOKUP(C5,Master,52,FALSE) =VLOOKUP(C5,Master,53,FALSE) =VLOOKUP(C5,Master,54,FALSE) =VLOOKUP(C5,Master,55,FALSE) =VLOOKUP(C5,Master,56,FALSE) =VLOOKUP(C5,Master,57,FALSE) =VLOOKUP(C5,Master,58,FALSE) Wa =VLOOKUP(C5,Master,59,FALSE) =VLOOKUP(C5,Master,60,FALSE) =VLOOKUP(C5,Master,61,FALSE) =VLOOKUP(C5,Master,62,FALSE) =VLOOKUP(C5,Master,63,FALSE) =VLOOKUP(C5,Master,64,FALSE) =VLOOKUP(C5,Master,65,FALSE) =VLOOKUP(C5,Master,66,FALSE) =VLOOKUP(C5,Master,67,FALSE) =VLOOKUP(C5,Master,68,FALSE) A13 =VLOOKUP(C5,Master,4,FALSE) =VLOOKUP(C5,Master,69,FALSE) =VLOOKUP(C5,Master,70,FALSE) =VLOOKUP(C5,Master,71,FALSE) =VLOOKUP(C5,Master,72,FALSE) =VLOOKUP(C5,Master,73,FALSE) =VLOOKUP(C5,Master,74,FALSE) =VLOOKUP(C5,Master,75,FALSE) =VLOOKUP(C5,Master,76,FALSE) =VLOOKUP(C5,Master,77,FALSE) =VLOOKUP(C5,Master,78,FALSE) Ar =VLOOKUP(C5,Master,79,FALSE) =VLOOKUP(C5,Master,80,FALSE) =VLOOKUP(C5,Master,81,FALSE) =VLOOKUP(C5,Master,82,FALSE) =VLOOKUP(C5,Master,83,FALSE) =VLOOKUP(C5,Master,84,FALSE) =VLOOKUP(C5,Master,85,FALSE) =VLOOKUP(C5,Master,86,FALSE) =VLOOKUP(C5,Master,87,FALSE) =VLOOKUP(C5,Master,88,FALSE) A15 =VLOOKUP(C5,Master,5,FALSE) =VLOOKUP(C5,Master,89,FALSE) =VLOOKUP(C5,Master,90,FALSE) =VLOOKUP(C5,Master,91,FALSE) =VLOOKUP(C5,Master,92,FALSE) =VLOOKUP(C5,Master,93,FALSE) =VLOOKUP(C5,Master,94,FALSE) =VLOOKUP(C5,Master,95,FALSE) A17 =COUNTA(Ingredients!A4:A200) =COUNTIF(Master!C4:C302,"Yes") Enter Part code below =(C19) Comprises A19 =VLOOKUP(C19,ing,3,FALSE) =VLOOKUP(C19,ing,4,FALSE) =VLOOKUP(C19,ing,5,FALSE) =VLOOKUP(C19,ing,6,FALSE) =VLOOKUP(C19,ing,7,FALSE) Des =VLOOKUP(C19,ing,8,FALSE) =VLOOKUP(C19,ing,9,FALSE) =VLOOKUP(C19,ing,10,FALSE) =VLOOKUP(C19,ing,11,FALSE) =VLOOKUP(C19,ing,12,FALSE) A21 =IF(ISNA(VLOOKUP(C19,ing,2,FALSE)),"Incorrect Part code",VLOOKUP(C19,ing,2,FALSE)) =VLOOKUP(C19,ing,13,FALSE) =VLOOKUP(C19,ing,14,FALSE) =VLOOKUP(C19,ing,15,FALSE) =VLOOKUP(C19,ing,16,FALSE) =VLOOKUP(C19,ing,17,FALSE) A23 E23 P23
I hope that I am not asking too much with this one.
I have a workbook with 3 worksheets, 2 containing data and 1 used as a search and display sheet. I enter information into a cell, press enter and the relevant data is displayed in various cells.
I would like to create a userform to do the job of the search/display sheet, this is mainly to make it look more professional!
What do I need to add to the userform? Textboxes, Labels, Command Buttons etc, etc.
How would I go about transferring/copying the following data into a userform?
Thanks
Ak
A1 B1 C1 D1 E1 P1 =TODAY() =NOW() A3 =COUNT(Master!A4:A301) Enter Product code below P N M E F S W S S G R A5 =VLOOKUP(C5,Master,7,FALSE) =VLOOKUP(C5,Master,8,FALSE) =VLOOKUP(C5,Master,9,FALSE) =VLOOKUP(C5,Master,10,FALSE) =VLOOKUP(C5,Master,11,FALSE) =VLOOKUP(C5,Master,12,FALSE) =VLOOKUP(C5,Master,13,FALSE) =VLOOKUP(C5,Master,14,FALSE) =VLOOKUP(C5,Master,15,FALSE) =VLOOKUP(C5,Master,16,FALSE) =VLOOKUP(C5,Master,17,FALSE) B M O M C M G H L S P A7 Product details =VLOOKUP(C5,Master,18,FALSE) =VLOOKUP(C5,Master,19,FALSE) =VLOOKUP(C5,Master,20,FALSE) =VLOOKUP(C5,Master,21,FALSE) =VLOOKUP(C5,Master,22,FALSE) =VLOOKUP(C5,Master,23,FALSE) =VLOOKUP(C5,Master,24,FALSE) =VLOOKUP(C5,Master,25,FALSE) =VLOOKUP(C5,Master,26,FALSE) =VLOOKUP(C5,Master,27,FALSE) =VLOOKUP(C5,Master,28,FALSE) Des A9 =IF(ISNA(VLOOKUP(C5,Master,2,FALSE)),"Incorrect Product code",VLOOKUP(C5,Master,2,FALSE)) =VLOOKUP(C5,Master,29,FALSE) =VLOOKUP(C5,Master,30,FALSE) =VLOOKUP(C5,Master,31,FALSE) =VLOOKUP(C5,Master,32,FALSE) =VLOOKUP(C5,Master,33,FALSE) =VLOOKUP(C5,Master,34,FALSE) =VLOOKUP(C5,Master,35,FALSE) =VLOOKUP(C5,Master,36,FALSE) =VLOOKUP(C5,Master,37,FALSE) =VLOOKUP(C5,Master,38,FALSE) AL =VLOOKUP(C5,Master,39,FALSE) =VLOOKUP(C5,Master,40,FALSE) =VLOOKUP(C5,Master,41,FALSE) =VLOOKUP(C5,Master,42,FALSE) =VLOOKUP(C5,Master,43,FALSE) =VLOOKUP(C5,Master,44,FALSE) =VLOOKUP(C5,Master,45,FALSE) =VLOOKUP(C5,Master,46,FALSE) =VLOOKUP(C5,Master,47,FALSE) =VLOOKUP(C5,Master,48,FALSE) A11 =VLOOKUP(C5,Master,3,FALSE) =VLOOKUP(C5,Master,49,FALSE) =VLOOKUP(C5,Master,50,FALSE) =VLOOKUP(C5,Master,51,FALSE) =VLOOKUP(C5,Master,52,FALSE) =VLOOKUP(C5,Master,53,FALSE) =VLOOKUP(C5,Master,54,FALSE) =VLOOKUP(C5,Master,55,FALSE) =VLOOKUP(C5,Master,56,FALSE) =VLOOKUP(C5,Master,57,FALSE) =VLOOKUP(C5,Master,58,FALSE) Wa =VLOOKUP(C5,Master,59,FALSE) =VLOOKUP(C5,Master,60,FALSE) =VLOOKUP(C5,Master,61,FALSE) =VLOOKUP(C5,Master,62,FALSE) =VLOOKUP(C5,Master,63,FALSE) =VLOOKUP(C5,Master,64,FALSE) =VLOOKUP(C5,Master,65,FALSE) =VLOOKUP(C5,Master,66,FALSE) =VLOOKUP(C5,Master,67,FALSE) =VLOOKUP(C5,Master,68,FALSE) A13 =VLOOKUP(C5,Master,4,FALSE) =VLOOKUP(C5,Master,69,FALSE) =VLOOKUP(C5,Master,70,FALSE) =VLOOKUP(C5,Master,71,FALSE) =VLOOKUP(C5,Master,72,FALSE) =VLOOKUP(C5,Master,73,FALSE) =VLOOKUP(C5,Master,74,FALSE) =VLOOKUP(C5,Master,75,FALSE) =VLOOKUP(C5,Master,76,FALSE) =VLOOKUP(C5,Master,77,FALSE) =VLOOKUP(C5,Master,78,FALSE) Ar =VLOOKUP(C5,Master,79,FALSE) =VLOOKUP(C5,Master,80,FALSE) =VLOOKUP(C5,Master,81,FALSE) =VLOOKUP(C5,Master,82,FALSE) =VLOOKUP(C5,Master,83,FALSE) =VLOOKUP(C5,Master,84,FALSE) =VLOOKUP(C5,Master,85,FALSE) =VLOOKUP(C5,Master,86,FALSE) =VLOOKUP(C5,Master,87,FALSE) =VLOOKUP(C5,Master,88,FALSE) A15 =VLOOKUP(C5,Master,5,FALSE) =VLOOKUP(C5,Master,89,FALSE) =VLOOKUP(C5,Master,90,FALSE) =VLOOKUP(C5,Master,91,FALSE) =VLOOKUP(C5,Master,92,FALSE) =VLOOKUP(C5,Master,93,FALSE) =VLOOKUP(C5,Master,94,FALSE) =VLOOKUP(C5,Master,95,FALSE) A17 =COUNTA(Ingredients!A4:A200) =COUNTIF(Master!C4:C302,"Yes") Enter Part code below =(C19) Comprises A19 =VLOOKUP(C19,ing,3,FALSE) =VLOOKUP(C19,ing,4,FALSE) =VLOOKUP(C19,ing,5,FALSE) =VLOOKUP(C19,ing,6,FALSE) =VLOOKUP(C19,ing,7,FALSE) Des =VLOOKUP(C19,ing,8,FALSE) =VLOOKUP(C19,ing,9,FALSE) =VLOOKUP(C19,ing,10,FALSE) =VLOOKUP(C19,ing,11,FALSE) =VLOOKUP(C19,ing,12,FALSE) A21 =IF(ISNA(VLOOKUP(C19,ing,2,FALSE)),"Incorrect Part code",VLOOKUP(C19,ing,2,FALSE)) =VLOOKUP(C19,ing,13,FALSE) =VLOOKUP(C19,ing,14,FALSE) =VLOOKUP(C19,ing,15,FALSE) =VLOOKUP(C19,ing,16,FALSE) =VLOOKUP(C19,ing,17,FALSE) A23 E23 P23
I am looking to do a VLOOKUP where the 2nd part of the table_array is located before the first part. Whenever I write formula Excel changes it and gives me an error message. Basically I am trying to do...
=VLOOKUP(C10,D:A,3,FALSE)
I am not sure if you can use a 3 or not but Excel keeps switching the D & A changing the formula to...
=VLOOKUP(C10,A:D,3,FALSE)
Still giving me an error for the 3
If I can't do what I want with VLOOKUP is there another way?
=VLOOKUP(C10,D:A,3,FALSE)
I am not sure if you can use a 3 or not but Excel keeps switching the D & A changing the formula to...
=VLOOKUP(C10,A:D,3,FALSE)
Still giving me an error for the 3
If I can't do what I want with VLOOKUP is there another way?
Hi!
I have 3 tables, Table1 is in page 1.
Table2 and Table3 are in page 2.
Table2 gets a value from Table3 and Table1 gets a value from Table2.
However my problem is that no matter which values i input in Table3, it always show the value 0 in Table1.
If it matters, here are the formulas my tables use
Table1[Utgifter] = SUBTOTAL(109; Table2[Utgifter])
Table2[Utgifter] = SUBTOTAL(109; Table3[Utgifter])
Table3[Utgifter] = [a]  [b]
('a' and 'b' are row values in Table3).
So why do i always get the value 0 in Table1 and how can i fix this?
Thanks!
I have 3 tables, Table1 is in page 1.
Table2 and Table3 are in page 2.
Table2 gets a value from Table3 and Table1 gets a value from Table2.
However my problem is that no matter which values i input in Table3, it always show the value 0 in Table1.
If it matters, here are the formulas my tables use
Table1[Utgifter] = SUBTOTAL(109; Table2[Utgifter])
Table2[Utgifter] = SUBTOTAL(109; Table3[Utgifter])
Table3[Utgifter] = [a]  [b]
('a' and 'b' are row values in Table3).
So why do i always get the value 0 in Table1 and how can i fix this?
Thanks!
Hello All!
A newbie here. I appreciate any help. I am actually quite good in a # of areas  but not in VLookups. I basically have two tabs with similar lists of customers and misc info. One list has addresses and the other doesn't. Each customer has a customer # reflected on both tabbed page. My formula wld basically be: if customer # on tab 1 ='s customer # on tab 2  take address from tab 1 and place in customer's row on tab 2.
Thanks 4 anyone helping me with this. I have already entitled the data in tab 1 (where the address is) "Table1."
A sample I was using to try to duplicate is as follows: (It however has a little more info in it which is confusing me) 
=IF(VLOOKUP(D3,Table1,1)=D3,IF(VLOOKUP(D3,Table1,2)=0,"DNP",VLOOKUP(D3,Table1,2)&IF(LEN(VLOOKUP(D3,Table1,2))=7,"",""&VLOOKUP(C3,table2,2))),"Missing GL Number")
The CUST# is in Column B in Table 1 (1st Tab) and in Column E in the target table. The address is in column I. I realize I will be putting the VLookup formula in column I of the target table.
Also, if it is fairly easy if someone cld interpret the above sample  I'd be very grateful.
Again  thank you 4 taking the time to help. I really appreciate it. Marty
A newbie here. I appreciate any help. I am actually quite good in a # of areas  but not in VLookups. I basically have two tabs with similar lists of customers and misc info. One list has addresses and the other doesn't. Each customer has a customer # reflected on both tabbed page. My formula wld basically be: if customer # on tab 1 ='s customer # on tab 2  take address from tab 1 and place in customer's row on tab 2.
Thanks 4 anyone helping me with this. I have already entitled the data in tab 1 (where the address is) "Table1."
A sample I was using to try to duplicate is as follows: (It however has a little more info in it which is confusing me) 
=IF(VLOOKUP(D3,Table1,1)=D3,IF(VLOOKUP(D3,Table1,2)=0,"DNP",VLOOKUP(D3,Table1,2)&IF(LEN(VLOOKUP(D3,Table1,2))=7,"",""&VLOOKUP(C3,table2,2))),"Missing GL Number")
The CUST# is in Column B in Table 1 (1st Tab) and in Column E in the target table. The address is in column I. I realize I will be putting the VLookup formula in column I of the target table.
Also, if it is fairly easy if someone cld interpret the above sample  I'd be very grateful.
Again  thank you 4 taking the time to help. I really appreciate it. Marty
Hi,
I would like to use the Vlookup function but unsure of how to add a condition. Depending upon what is in one cell (a number between 1  9, which corresponds to a separate name range on a different worksheet (e.g. 1 = Rank1Opponent; 2 = Rank2Opponent...) affects which table_array is used in the lookup.
I've included the basic Vlookup, which works as it stands:
=VLOOKUP(A2, Rank1Opponent ,2,FALSE)
At the moment, the easiest thing for me to do is add additional columns with the Vlookup function referencing different name ranges (i.e. =VLOOKUP(A2,Rank2Opponent,2,FALSE) etc.) but I think this is probably a long winded way of doing it.
I thought about using nested IF statements but wondered if there was a more 'efficient' way of doing it?
Any advice would be greatly appreciated,
Cheers
I would like to use the Vlookup function but unsure of how to add a condition. Depending upon what is in one cell (a number between 1  9, which corresponds to a separate name range on a different worksheet (e.g. 1 = Rank1Opponent; 2 = Rank2Opponent...) affects which table_array is used in the lookup.
I've included the basic Vlookup, which works as it stands:
=VLOOKUP(A2, Rank1Opponent ,2,FALSE)
At the moment, the easiest thing for me to do is add additional columns with the Vlookup function referencing different name ranges (i.e. =VLOOKUP(A2,Rank2Opponent,2,FALSE) etc.) but I think this is probably a long winded way of doing it.
I thought about using nested IF statements but wondered if there was a more 'efficient' way of doing it?
Any advice would be greatly appreciated,
Cheers
Hi all,
I'm hoping this is just a quick and easy thing I'm missing but I cannot seem to figure out how to use a concatination within the table_array of a Vlookup.
So far I have
PHP Code:
=IF(ISNA(VLOOKUP(A2&H2&I2,Billed!AE:AE,1,FALSE)),"Not Billed","Billed")
What I am trying to do is replace the "Billed!AE:AE" part with "Billed!A2&H2&I2" but I am unsure if this is correct and if so what does the col_index_num become?
Thanks in advanced.
Guy
I'm hoping this is just a quick and easy thing I'm missing but I cannot seem to figure out how to use a concatination within the table_array of a Vlookup.
So far I have
PHP Code:
=IF(ISNA(VLOOKUP(A2&H2&I2,Billed!AE:AE,1,FALSE)),"Not Billed","Billed")
What I am trying to do is replace the "Billed!AE:AE" part with "Billed!A2&H2&I2" but I am unsure if this is correct and if so what does the col_index_num become?
Thanks in advanced.
Guy
Hi folks
I am wondering is it possible to use a variable value for the table_array input on a vlookupargument.
So for instance the vlookup argument is
=vlookup(lookup_value, table_array, col_index_num, [range_lookup])
and I would like to have one of several arrays used depending what is selected in a drop down box.
Can this be done or could someone suggest a different way of doing it...
Thanks!
senan_g
I am wondering is it possible to use a variable value for the table_array input on a vlookupargument.
So for instance the vlookup argument is
=vlookup(lookup_value, table_array, col_index_num, [range_lookup])
and I would like to have one of several arrays used depending what is selected in a drop down box.
Can this be done or could someone suggest a different way of doing it...
Thanks!
senan_g
I looked through several pages of VLOOKUP and couldn't find anything other that doing VBA MULTLOOKUP and was wondering if possible...
I have a part number on one worksheet that looks at another sheet for the same part number to return the yearly usage.
BUT, sometimes there are multiple customers on that sheet that use the same part number. Only the first result is returned.
So right now, it's
=VLOOKUP(C337,forecast!D:CW,98,FALSE)
Yes, I have lots of columns...
Any simple way of having the vlookup return the sum of the one PN from several rows in column 98?
I have a part number on one worksheet that looks at another sheet for the same part number to return the yearly usage.
BUT, sometimes there are multiple customers on that sheet that use the same part number. Only the first result is returned.
So right now, it's
=VLOOKUP(C337,forecast!D:CW,98,FALSE)
Yes, I have lots of columns...
Any simple way of having the vlookup return the sum of the one PN from several rows in column 98?
Hi there,
I am trying to do a vlookup that currently looks as follows:
Code:
However, the selection of the table_array (SAP!A$2:AA$42) is a set value. Instead I would like to select all the data that is on sheet SAP. The idea is that the data set will not always be A2:AA42, so I need to make this part variable.
Normally I would use
Code:
to select the entire data set.
How do I combine these two?
Please note that I will be copying the the vlookup formula down for a bunch of cells.
regards,
Jay
I am trying to do a vlookup that currently looks as follows:
Code:
Range("C2").Formula = "=VLOOKUP(A2,SAP!A$2:AA$42,2,FALSE)"
However, the selection of the table_array (SAP!A$2:AA$42) is a set value. Instead I would like to select all the data that is on sheet SAP. The idea is that the data set will not always be A2:AA42, so I need to make this part variable.
Normally I would use
Code:
Range(Selection, Selection.End(xlDown)).Select Range(Selection, Selection.End(xlToRight)).Select
to select the entire data set.
How do I combine these two?
Please note that I will be copying the the vlookup formula down for a bunch of cells.
regards,
Jay
My VBA is so rusty!! need help on the Findfirst syntax
ok I have two tables, table1 and table2. Both have a field called "CKey". Basically I am cycling through each record in Table1, to find the "CKey" in table2 and do something.
I have written:
table2.MoveFirst
Do While Not table2.EOF
Message2 = "Working on record " & counter & " of " & lastrec
DoEvents
strFindKey = table2!CKey
strCriteria = "Ckey='" & strFindKey & "'"
table3.FindFirst strCriteria
If Not table3.NoMatch Then....
I get this message "The Microsoft Jet Engine does not recognise "CKey" as an expression ....."
What's going wrong? Help!!
ok I have two tables, table1 and table2. Both have a field called "CKey". Basically I am cycling through each record in Table1, to find the "CKey" in table2 and do something.
I have written:
table2.MoveFirst
Do While Not table2.EOF
Message2 = "Working on record " & counter & " of " & lastrec
DoEvents
strFindKey = table2!CKey
strCriteria = "Ckey='" & strFindKey & "'"
table3.FindFirst strCriteria
If Not table3.NoMatch Then....
I get this message "The Microsoft Jet Engine does not recognise "CKey" as an expression ....."
What's going wrong? Help!!
I have a spreadsheet that has a series of single level bill of materials. Column A is the part number, Column B is the bill of material level, Column C is the parent for the part number, column D is the lead time/processing time for the part (total time to order the part and assemble it into the parent). I am trying to calculate the total lead time for each part using a formula in column E. For an example, a level 3 part would add together its lead time, the lead time for its parent (level 2 part), and the lead time of the parent's parent (level 1 part). Note: we are not considering level 0 since assembling all of the subassemblies to it takes relatively no time.
If the parts were listed in order from parent to lowest level child or child to parent, I have seen solutions here for calculating BOM quantities that could be potentially adapted to sum lead times, but that is not the case here  the parts can be listed in any order. My current solution is not elegant whatsoever and only goes down to a level 5 part. It is a series of progessively more nested VLOOKUP terms that are summed:
=D2+IF(B2>1,VLOOKUP(C2,A$2:D$41,4,FALSE),0)+IF(B2>2,VLOOKUP(VLOOKUP(C2,A$2:C$41,3,FALSE),A$2:D$41,4,FALSE),0)+IF(B2>3,VLOOKUP(VLOOKUP(VLOOKUP(C2,A$2:C$41,3,FALSE),A$2:C$41,3,FALSE),A$2:D$41,4,FALSE),0)+IF(B2>4,VLOOKUP(VLOOKUP(VLOOKUP(VLOOKUP(C2,A$2:C$41,3,FALSE),A$2:C$41,3,FALSE),A$2:C$41,3,FALSE),A$2:D$41,4,FALSE),0)+IF(B2>5,VLOOKUP(VLOOKUP(VLOOKUP(VLOOKUP(VLOOKUP(C2,A$2:C$41,3,FALSE),A$2:C$41,3,FALSE),A$2:C$41,3,FALSE),A$2:C$41,3,FALSE),A$2 :D$41,4,FALSE),0)
If I end up having a level 6 part, I will need to add an even longer term onto the end of this formula and if I end up with a level 7 part at some future point, I think the formula will run too long to fit in a cell. Is there a better way of doing this? I have been racking my brain thinking that SUMPRODUCT can somehow come to the rescue, but have come up empty so far.
If the parts were listed in order from parent to lowest level child or child to parent, I have seen solutions here for calculating BOM quantities that could be potentially adapted to sum lead times, but that is not the case here  the parts can be listed in any order. My current solution is not elegant whatsoever and only goes down to a level 5 part. It is a series of progessively more nested VLOOKUP terms that are summed:
=D2+IF(B2>1,VLOOKUP(C2,A$2:D$41,4,FALSE),0)+IF(B2>2,VLOOKUP(VLOOKUP(C2,A$2:C$41,3,FALSE),A$2:D$41,4,FALSE),0)+IF(B2>3,VLOOKUP(VLOOKUP(VLOOKUP(C2,A$2:C$41,3,FALSE),A$2:C$41,3,FALSE),A$2:D$41,4,FALSE),0)+IF(B2>4,VLOOKUP(VLOOKUP(VLOOKUP(VLOOKUP(C2,A$2:C$41,3,FALSE),A$2:C$41,3,FALSE),A$2:C$41,3,FALSE),A$2:D$41,4,FALSE),0)+IF(B2>5,VLOOKUP(VLOOKUP(VLOOKUP(VLOOKUP(VLOOKUP(C2,A$2:C$41,3,FALSE),A$2:C$41,3,FALSE),A$2:C$41,3,FALSE),A$2:C$41,3,FALSE),A$2 :D$41,4,FALSE),0)
If I end up having a level 6 part, I will need to add an even longer term onto the end of this formula and if I end up with a level 7 part at some future point, I think the formula will run too long to fit in a cell. Is there a better way of doing this? I have been racking my brain thinking that SUMPRODUCT can somehow come to the rescue, but have come up empty so far.
Hello,
I am new to excel and am struggling with vlookup. I have looked at various posts regarding vlookup but couldn't successfully use it to find a solution to my problem. The below is the description of my problem. I have a table (table1) with material numbers which have a price . This value is time dependent i.e., a material 999 could have a price of $10 for 1/1/20081/15/2008 and $20 for 1/16/1008  1/31/2008.
A B C D
999 1/1/2008 1/15/2008 $10
999 1/15/2008 1/31/2008 $20
998 2/1/2008  2/25/2008 $15
I have another table (table2) in another sheet in the same workbook have a material and date.
A B C
999 1/10/2008
999 1/20/2008
998 2/15/2008
My requirement to take the material value and date in table2 and match it with table1 and get the value of column D in table 1 to column C of table2.
I have tried using vlookup but it only works for the first match and doesn't check for other values
below is the function that i tried
=if(and(vlookup(A2,Sheet2!A1:D4,2,false)<=Sheet1!B2,vlookup(Sheet1!A2,Sheet2!A2:D4,3,false)>=Sheet1! C2)),vlookup(Sheet1!A2,Sheet2!A2:D4,4,false),"error")
Can anyone please help me with this problem.
I am new to excel and am struggling with vlookup. I have looked at various posts regarding vlookup but couldn't successfully use it to find a solution to my problem. The below is the description of my problem. I have a table (table1) with material numbers which have a price . This value is time dependent i.e., a material 999 could have a price of $10 for 1/1/20081/15/2008 and $20 for 1/16/1008  1/31/2008.
A B C D
999 1/1/2008 1/15/2008 $10
999 1/15/2008 1/31/2008 $20
998 2/1/2008  2/25/2008 $15
I have another table (table2) in another sheet in the same workbook have a material and date.
A B C
999 1/10/2008
999 1/20/2008
998 2/15/2008
My requirement to take the material value and date in table2 and match it with table1 and get the value of column D in table 1 to column C of table2.
I have tried using vlookup but it only works for the first match and doesn't check for other values
below is the function that i tried
=if(and(vlookup(A2,Sheet2!A1:D4,2,false)<=Sheet1!B2,vlookup(Sheet1!A2,Sheet2!A2:D4,3,false)>=Sheet1! C2)),vlookup(Sheet1!A2,Sheet2!A2:D4,4,false),"error")
Can anyone please help me with this problem.
I'm having a problem getting my table_array to stay constant. It keeps going down one cell every time I try to CTRL+D the formula. The only variable I want to change in this VLOOKUP is the lookup_value. For example:
Here is my C2 Cell:
=VLOOKUP(A2, B2:C100,2,False)
And in C3 (When I try to CTRL + D) It will change to:
=VLOOKUP(A3,B3:C101,2,False)
But this is how I would like to get it to work(where the table array is constant):
=VLOOKUP(A3, B2:C100,2,False)
Any help would be much appreciated.
Here is my C2 Cell:
=VLOOKUP(A2, B2:C100,2,False)
And in C3 (When I try to CTRL + D) It will change to:
=VLOOKUP(A3,B3:C101,2,False)
But this is how I would like to get it to work(where the table array is constant):
=VLOOKUP(A3, B2:C100,2,False)
Any help would be much appreciated.
Hello, I am trying to "manually" enter the table_array in a VLOOKUP function. I basically want to be able to define the table_array using functions nested within the VLOOKUP.
For simplicity, I want to do a VLOOKUP over a range of let's say... A1:C3. I can use the following in a cell by itself to produce "A1:C3"
A B
x 1
y 3
=CONCATENATE("A",B2,":","C",B3)
and
=ADDRESS(1,B1,4)&":"&ADDRESS(3,B2,4)
These both return A1:C3 for me, but when I embed them in the argument of the VLOOKUP, I get a #VALUE error.
I have tried using TEXT() to convert the output into text, I have tried leaving the result A1:C3 in a cell and referencing that cell in the table_array argument of VLOOKUP... nothing seems to work.
=VLOOKUP(C21, ADDRESS(1,B1,4)&":"&ADDRESS(3,B2,4), 7)
Note: in the example above, C21 is the value I want to find in the first row and the 7th column contains the data I want to look up. If I do manually enter in the range A1:C3 in place of the ADDRESS functions, the formula works fine... so it has to do with calculating the table_array
I know it seems obvious that I should just type in A1:C3, but I want to be able to calculate the range based on other criteria and don't want to have to manually enter the range for each VLOOKUP.
Can anyone help?
For simplicity, I want to do a VLOOKUP over a range of let's say... A1:C3. I can use the following in a cell by itself to produce "A1:C3"
A B
x 1
y 3
=CONCATENATE("A",B2,":","C",B3)
and
=ADDRESS(1,B1,4)&":"&ADDRESS(3,B2,4)
These both return A1:C3 for me, but when I embed them in the argument of the VLOOKUP, I get a #VALUE error.
I have tried using TEXT() to convert the output into text, I have tried leaving the result A1:C3 in a cell and referencing that cell in the table_array argument of VLOOKUP... nothing seems to work.
=VLOOKUP(C21, ADDRESS(1,B1,4)&":"&ADDRESS(3,B2,4), 7)
Note: in the example above, C21 is the value I want to find in the first row and the 7th column contains the data I want to look up. If I do manually enter in the range A1:C3 in place of the ADDRESS functions, the formula works fine... so it has to do with calculating the table_array
I know it seems obvious that I should just type in A1:C3, but I want to be able to calculate the range based on other criteria and don't want to have to manually enter the range for each VLOOKUP.
Can anyone help?
Hi, I need a way to select a discontinuous range in VBA with these circumstances:
The data I'm working with is grouped into 5 by 5 regions, each region with a header above it, stacked one above the other with an empty line between each
There are also several "columns" of these tables, although I could put them on separate sheets if that simplifies the code
What I want to do is find the maximum value within a given location in each table. By this, I mean that I will be comparing the value in 1st row in the 1st column of the 1st table with the value in the 1st row in the 1st column of the 2nd table.
I can't seem to upload files here at work, but the tables look something like...
Table1
1 2 3 4 5
6 7 8 9 0
1 2 3 4 5
1 2 3 4 5
1 2 3 4 5
Table2
6 7 0 9 0
1 2 3 4 5
6 7 0 9 0
6 7 0 9 0
6 7 0 9 0
With the end product being
Table2 Table2 Table1 Table2 Table2
Table1 Table1 Table1 Table1 Table1
Table2 Table2 Table1 Table2 Table2
Table2 Table2 Table1 Table2 Table2
Table2 Table2 Table1 Table2 Table2
Thanks in advance, sorry for the mess
The data I'm working with is grouped into 5 by 5 regions, each region with a header above it, stacked one above the other with an empty line between each
There are also several "columns" of these tables, although I could put them on separate sheets if that simplifies the code
What I want to do is find the maximum value within a given location in each table. By this, I mean that I will be comparing the value in 1st row in the 1st column of the 1st table with the value in the 1st row in the 1st column of the 2nd table.
I can't seem to upload files here at work, but the tables look something like...
Table1
1 2 3 4 5
6 7 8 9 0
1 2 3 4 5
1 2 3 4 5
1 2 3 4 5
Table2
6 7 0 9 0
1 2 3 4 5
6 7 0 9 0
6 7 0 9 0
6 7 0 9 0
With the end product being
Table2 Table2 Table1 Table2 Table2
Table1 Table1 Table1 Table1 Table1
Table2 Table2 Table1 Table2 Table2
Table2 Table2 Table1 Table2 Table2
Table2 Table2 Table1 Table2 Table2
Thanks in advance, sorry for the mess
I have set up a vlookup table which feeds off lists in another worksheet. The vlookup formula is in the main table.
Prior to any additions the vlookup would enter a blank cell as "" (i.e.blank), however, after making additional entries the new additions are being reported as 0 (zero) in cells which do not (purposefully) include a variable.
I can't understand why this has happened and would prefer to have blank cells in those that have no variable.
The vlookup formulas a
=VLOOKUP($D7,VLookupList1,2,FALSE)
=VLOOKUP($D7,VLookupList1,3,FALSE)
=VLOOKUP($D7,VLookupList1,4,FALSE)
=VLOOKUP($D7,VLookupList1,5,FALSE)
Any ideas?
Prior to any additions the vlookup would enter a blank cell as "" (i.e.blank), however, after making additional entries the new additions are being reported as 0 (zero) in cells which do not (purposefully) include a variable.
I can't understand why this has happened and would prefer to have blank cells in those that have no variable.
The vlookup formulas a
=VLOOKUP($D7,VLookupList1,2,FALSE)
=VLOOKUP($D7,VLookupList1,3,FALSE)
=VLOOKUP($D7,VLookupList1,4,FALSE)
=VLOOKUP($D7,VLookupList1,5,FALSE)
Any ideas?