Vlookup Newbie Question 


Vlookup Newbie Question  Excel 
View Answers 
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
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) ...
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
Basic Web Query in Excel  Import Data from the Web into Excel
 Import data from the web into Excel with this macro. This is a basic web query macro that will import data from any web
 Import data from the web into Excel with this macro. This is a basic web query macro that will import data from any web
Display The Actual Link / Email Address From Links in Excel  UDF
 Display the actual link or email address from links within Excel with this UDF. This user defined function pulls the ac
 Display the actual link or email address from links within Excel with this UDF. This user defined function pulls the ac
Send Emails through Outlook using Email Addresses from Excel and text from Word
 This macro allows you to send an email to a list of recipients through excel. The email will be sent through Outlook an
 This macro allows you to send an email to a list of recipients through excel. The email will be sent through Outlook an
Sort Data With Headers in Ascending Order in Excel
 Macro that sorts data that has headers in ascending order in Excel. This macro assumes that you data has headers on it.
 Macro that sorts data that has headers in ascending order in Excel. This macro assumes that you data has headers on it.
Similar Topics
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?
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
i create two table ,table1 contains following data.
Table1 item no customer invoice qty DM1002 EVG 125 500 SS7002 EVG 126 600 SOMITB1 EVG 127 300 BUK1005 BUK 128 300 BUK1006 BUK 129 500
table2 contains following three headings of data
Table2 customer invoice qty
in table2 i want to get the customer name from first table so i create a simple relation between two tables.
i drag customer from table1 and leave it on table2 and i table2 design view i click customer heading and then in raw sorce i select table1 and in table1 it shows me drop down list of item no not for customer.
please tell me what's going wrong.
thanks.
Table1 item no customer invoice qty DM1002 EVG 125 500 SS7002 EVG 126 600 SOMITB1 EVG 127 300 BUK1005 BUK 128 300 BUK1006 BUK 129 500
table2 contains following three headings of data
Table2 customer invoice qty
in table2 i want to get the customer name from first table so i create a simple relation between two tables.
i drag customer from table1 and leave it on table2 and i table2 design view i click customer heading and then in raw sorce i select table1 and in table1 it shows me drop down list of item no not for customer.
please tell me what's going wrong.
thanks.
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 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
Hi all,
I'm using excel 2007 (but this shouldn't be an issue), I have created an unique ID for each of my Customers on "Customers table".
I want to look at the table and place in another table all the most important info on each customer e.g.
customer id Address Last Order Price
The sells info is on the sales table, so I want to lookup customer id on first table and get the sell data from the second table.
I tried =VLOOKUP(A1,'customer table!'A1:C45,5,False)
A1 is the customer id and 5 is the column number which could return the address, but all I'm getting is the return #N/A.
I'm using excel 2007 (but this shouldn't be an issue), I have created an unique ID for each of my Customers on "Customers table".
I want to look at the table and place in another table all the most important info on each customer e.g.
customer id Address Last Order Price
The sells info is on the sales table, so I want to lookup customer id on first table and get the sell data from the second table.
I tried =VLOOKUP(A1,'customer table!'A1:C45,5,False)
A1 is the customer id and 5 is the column number which could return the address, but all I'm getting is the return #N/A.
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'm looking for a way to add information to a vlookup.
What I have is a huge customer list with addresses, ph#, fax, email etc. I then have a vlookup setup in a group of cells, in which we drop drown list select customer, and the rest of the fields (address,ph#,fax etc) vlookup based on customer name. However, rather than updating any customer info on the large table, I'd like to be able to add the updated info back to the table. (to update blank or outdated customer information)
I will also need to be adding new customers to my drop down list, but I think I've found ways to do this. (aside from adding the vlookup info to that line) If I have it add to the data list can I have it auto alphabetical order the new entry?
this is the method I've found for adding the the validation list. http://www.brainbell.com/tutorials/m...ation_List.htm.
Any help and suggestions would be greatly appreciated, Thanks.
What I have is a huge customer list with addresses, ph#, fax, email etc. I then have a vlookup setup in a group of cells, in which we drop drown list select customer, and the rest of the fields (address,ph#,fax etc) vlookup based on customer name. However, rather than updating any customer info on the large table, I'd like to be able to add the updated info back to the table. (to update blank or outdated customer information)
I will also need to be adding new customers to my drop down list, but I think I've found ways to do this. (aside from adding the vlookup info to that line) If I have it add to the data list can I have it auto alphabetical order the new entry?
this is the method I've found for adding the the validation list. http://www.brainbell.com/tutorials/m...ation_List.htm.
Any help and suggestions would be greatly appreciated, Thanks.
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 change the Named Range in vlookup formulas using something like indirect reference. My formula is:
=vlookup(b4, Table1,4)*b4vlookup(b4,Table1,2)
the named range need to be changed dynamically. there are 14 tables. Table1,Table2,Table3...
Can anyone help?
All 
Two tables of vendors. Both tables have corresponding vendors #s.
Table A has addresses  Table B doesn't.
I have started VLOOKUP(D3,Table1,...)=J3,Table2,'place' L3,Table1 in M3,Table2.
I clearly don't know what I am doing at this point  but I am hoping that a fix is not that difficult. Thanks! Marty
Two tables of vendors. Both tables have corresponding vendors #s.
Table A has addresses  Table B doesn't.
I have started VLOOKUP(D3,Table1,...)=J3,Table2,'place' L3,Table1 in M3,Table2.
I clearly don't know what I am doing at this point  but I am hoping that a fix is not that difficult. Thanks! Marty
I have this cell formula:
Code:
This code allows me to search for a record without having to place the entire record in cell A1. Also, if there is more than one record that starts with the given entry it will say "Need More".
However I would like to have the results of the entry if there are two possible records not just one.
For example, in cells A3 and A4 there would be formulas like this:
A3 = =if(countif(Table1,A1&"*")=2,VLOOKUP(A1&"*",Table1,1,First)),"N/A")
A4 =if(countif(Table1,A1&"*")=2,VLOOKUP(A1&"*",Table1,1,Second)),"N/A")
Did I explain that well enough. Please ask any questions. I am looking forward to this answer!
Thanks!
Nicole
Code:
=if(countif(Table1,A1&"*")>1,"Need More",VLOOKUP(A1&"*",Table1,1,FALSE))
This code allows me to search for a record without having to place the entire record in cell A1. Also, if there is more than one record that starts with the given entry it will say "Need More".
However I would like to have the results of the entry if there are two possible records not just one.
For example, in cells A3 and A4 there would be formulas like this:
A3 = =if(countif(Table1,A1&"*")=2,VLOOKUP(A1&"*",Table1,1,First)),"N/A")
A4 =if(countif(Table1,A1&"*")=2,VLOOKUP(A1&"*",Table1,1,Second)),"N/A")
Did I explain that well enough. Please ask any questions. I am looking forward to this answer!
Thanks!
Nicole
Hi All,
I'm new to this forum and I would like if I could get some help.
Is there a way to check if value exists in Vlookup Table. I have two sheet in my workbook, one is Customer sheet and other is just Sales Type sheet with Vlookup Table. My Customer sheet has customer code column, that code is used to assign sales type from Vlookup Table.
Now, my report is dynamic and changes from week to week, so this makes my Vlookup table give me a output error #N/A so I have to go in and update that table again, again, and again.
Is there a way to check if value exists in VLookUp table before I actually use that table. For example:
If value (customer code) does not exist in VLookUp table than copy that customer code on last available cell in the VlookUp table. This should be done untill all missing customer codes are not in VLookUp table. After all customer codes are available in VlookUp table then I would assign sales type by running VlookUp.
Is this possible with marco ili vb or something like that.
Any help on this would be greatly appreciated.
I'm new to this forum and I would like if I could get some help.
Is there a way to check if value exists in Vlookup Table. I have two sheet in my workbook, one is Customer sheet and other is just Sales Type sheet with Vlookup Table. My Customer sheet has customer code column, that code is used to assign sales type from Vlookup Table.
Now, my report is dynamic and changes from week to week, so this makes my Vlookup table give me a output error #N/A so I have to go in and update that table again, again, and again.
Is there a way to check if value exists in VLookUp table before I actually use that table. For example:
If value (customer code) does not exist in VLookUp table than copy that customer code on last available cell in the VlookUp table. This should be done untill all missing customer codes are not in VLookUp table. After all customer codes are available in VlookUp table then I would assign sales type by running VlookUp.
Is this possible with marco ili vb or something like that.
Any help on this would be greatly appreciated.
Hi, I got stuck with the following problem:
I have a table (TABLE1) with two entries: columns are group of clients and rows are tipe of products. The table is filled with target margin I want for each case (client group/product type).
Now I have another sheet with the list of all articles, with a column indicating the type (matching the types of the rows in TABLE1) and a single cell where I can put the client group (matching the columns of TABLE1). Now I would like to get in another column the target margin from TABLE1, for each article, once the group client chosen.
SUMPRODUCT doesnt work since I am crossing rows and columns of TABLE1. Neither VLOOKUP or HLOOKUP since each one would give me only part of information.
I thought of modifying TABLE1 putting each combination productclienttarget in a different row, but I would loose the compact form of the table.
If anyone can help me it would be greatly apreciated.
Regards,
Antonio
I have a table (TABLE1) with two entries: columns are group of clients and rows are tipe of products. The table is filled with target margin I want for each case (client group/product type).
Now I have another sheet with the list of all articles, with a column indicating the type (matching the types of the rows in TABLE1) and a single cell where I can put the client group (matching the columns of TABLE1). Now I would like to get in another column the target margin from TABLE1, for each article, once the group client chosen.
SUMPRODUCT doesnt work since I am crossing rows and columns of TABLE1. Neither VLOOKUP or HLOOKUP since each one would give me only part of information.
I thought of modifying TABLE1 putting each combination productclienttarget in a different row, but I would loose the compact form of the table.
If anyone can help me it would be greatly apreciated.
Regards,
Antonio
I have two Tables, Table1 and Table2, in a single Excel spreadsheet. Table1 is our master log, and Table2 contains only Provider Names, Contact Names, Phone Numbers, and Email Addresses. I need to compare column K from Table1 with column AT from Table2. Whenever Excel finds a perfect match, I need Excel to copy AW:AY to AB:AD. For each value in column AT, there will be several identical matches in column K.
I have search long hours for a solution, have tried to implement vlookup() and index(match()), but cannot figure either one of them. Any help would be greatly appreciated.
fyi, Table1 is almost 1500 rows long, while Table2 is not quite 80.
Hi,
I was using vlookup function which is workingfine except one issue.
My Main report, Student wise, has a table structure of:
Table1
Name Maths Physics Social
Guy Phil 61 82 71
Guy Van 20
and my Subject wise report is:
Name Guy Van Guy Phil
Physics 82 56
Social 71 70
Maths 61 34
Now my query is, I am able to use vlookupin in Table1 =VLOOKUP(B2,$A$6:$C$9,2,FALSE)) and change the col_index_num manually as Table2 doesn't follow same column sequence always. But Can I get column Number (col_index_num) based on the student name in Table2?
E.g; If student GuyVan, the use the column number where his name is present (in the tablearray)?
VLOOKUP(lookup_value,table_array,col_index_num,range_lookup)
Let me know if you need more info.
Rgds.
I was using vlookup function which is workingfine except one issue.
My Main report, Student wise, has a table structure of:
Table1
Name Maths Physics Social
Guy Phil 61 82 71
Guy Van 20
and my Subject wise report is:
Name Guy Van Guy Phil
Physics 82 56
Social 71 70
Maths 61 34
Now my query is, I am able to use vlookupin in Table1 =VLOOKUP(B2,$A$6:$C$9,2,FALSE)) and change the col_index_num manually as Table2 doesn't follow same column sequence always. But Can I get column Number (col_index_num) based on the student name in Table2?
E.g; If student GuyVan, the use the column number where his name is present (in the tablearray)?
VLOOKUP(lookup_value,table_array,col_index_num,range_lookup)
Let me know if you need more info.
Rgds.
Hi Guys,
Is there a way to check if value exists in Vlookup Table. I have two sheet in my workbook, one is Customer sheet and other is just Sales Type sheet with Vlookup Table. My Customer sheet has customer code column, that code is used to assign sales type from Vlookup Table.
Now, my report is dynamic and change from week to week, so this makes my Vlookup table give me a error output #N/A so I have to go in and update that table again, again, and again.
Is there a way to check if value exists in VLookUp table before I actually use that table.
If value (customer code) does not exist in VLookUp table than copy that customer code on last available cell in the VlookupUp table. This should be done untill all missing customer codes are not in VLookUp table. After all customer codes are available in VlookUp table then I would assign sales type by running VlookUp.
Is this possible with marco ili vb to run by pushing button to something like that.
Any help on this would be greatly appreciated.
Is there a way to check if value exists in Vlookup Table. I have two sheet in my workbook, one is Customer sheet and other is just Sales Type sheet with Vlookup Table. My Customer sheet has customer code column, that code is used to assign sales type from Vlookup Table.
Now, my report is dynamic and change from week to week, so this makes my Vlookup table give me a error output #N/A so I have to go in and update that table again, again, and again.
Is there a way to check if value exists in VLookUp table before I actually use that table.
If value (customer code) does not exist in VLookUp table than copy that customer code on last available cell in the VlookupUp table. This should be done untill all missing customer codes are not in VLookUp table. After all customer codes are available in VlookUp table then I would assign sales type by running VlookUp.
Is this possible with marco ili vb to run by pushing button to something like that.
Any help on this would be greatly appreciated.
Hi,
I have the list of data in two different worksheets.
First sheet is something like this
ANGKANH
KAB_PHLUK
PHUM_PRAMMUOY_DAB
KHNANG_TA_KONG
And second sheet is something like below
A41_2Gi_ANGKANH ID001
A41_2Gi_KAB_PHLUK ID002
A41_2Gi_PHUM_PRAMMUOY_DAB ID003
A41_2Gi_KHNANG_TA_KONG ID004
I would like to use vlookup to get ID from table2 to table1. Is it possible to ask excel to cheek if any values in the table1 are very much similar to the any values in the table2 then start vlookup?
Thanks you very much for your time. Your comment will be very much appreciated.
I have the list of data in two different worksheets.
First sheet is something like this
ANGKANH
KAB_PHLUK
PHUM_PRAMMUOY_DAB
KHNANG_TA_KONG
And second sheet is something like below
A41_2Gi_ANGKANH ID001
A41_2Gi_KAB_PHLUK ID002
A41_2Gi_PHUM_PRAMMUOY_DAB ID003
A41_2Gi_KHNANG_TA_KONG ID004
I would like to use vlookup to get ID from table2 to table1. Is it possible to ask excel to cheek if any values in the table1 are very much similar to the any values in the table2 then start vlookup?
Thanks you very much for your time. Your comment will be very much appreciated.
I am fairly new to Excel. I have a spreadsheet where column A has our customer account numbers listed. columns B and C are the invoice numbers and amount. Each week I print each customers invoices for that week. I used the subtotal and insert a page break after each new customer so once the data is sorted by customer I can just print and each new customer number starts a new page. On another worksheet I have each stores account number and information. I am doing a vlookup where the customer account number brings over the customer information. This works fine but once there is a new customer it still brings the first customer info. What I am trying to find out is if there is a way to have it do a new lookup each time there is a page break or if vlookup is not the rout to go in this case.
Hi there, can anybody give me a better way of constructing this formula?
Code:
I want to sum several vlookups and having to repeat the whole vlookup formula for each one seems inefficient to me.
Code:
=SUM(VLOOKUP(4040,Table1[#All],16,FALSE)+VLOOKUP(1510,Table1[#All],16,FALSE))
I want to sum several vlookups and having to repeat the whole vlookup formula for each one seems inefficient to me.
I have come across a rather weird problem with a VLookUp table.
I am trying to create a worksheet that converts a list of customer numbers into customer names. For this purpose I created a table with customer numbers in the first column and customer names in the second column. Now I am using the VLookup command like this:
=VLOOKUP(E4,A$2:B$660,2)
The number you would like to get the name for is entered in cell E4 and the table with names and customers starts at A2 and ends at B660 with numbers in column 1 and names in column 2. Now when I enter a number in E4, it returns the wrong name even though the number has an exact match in the table. When I copy and past the number straight from the table, I get the right response. Both cells have the same format.
Anybody know why this happens?
Thanks a lot!
I am trying to create a worksheet that converts a list of customer numbers into customer names. For this purpose I created a table with customer numbers in the first column and customer names in the second column. Now I am using the VLookup command like this:
=VLOOKUP(E4,A$2:B$660,2)
The number you would like to get the name for is entered in cell E4 and the table with names and customers starts at A2 and ends at B660 with numbers in column 1 and names in column 2. Now when I enter a number in E4, it returns the wrong name even though the number has an exact match in the table. When I copy and past the number straight from the table, I get the right response. Both cells have the same format.
Anybody know why this happens?
Thanks a lot!
Hello everyone,
I have two tables, Table1 contains following fields: Item #, Warehouse #, Qty O/H, etc... and Table2 has Item #, Warehouse #, Qty sold, etc... Since, in Table1 and Table2, There are multiple line items for each Item # do to many warehouses.
Now, I need to pull the Qty Sold info. from Table2 into Table1 by matching Item # and warehouse for that Item #. I.e. Item # is in Column 1 and warehouse is in column 2 of Table 1 and same with Table2 the Item# is in Column1 and warehouse # is in column2 and Qty Sold is in Column 3.
Is this possible? Can you please help......
Thank you in advance.
Aman
I have two tables, Table1 contains following fields: Item #, Warehouse #, Qty O/H, etc... and Table2 has Item #, Warehouse #, Qty sold, etc... Since, in Table1 and Table2, There are multiple line items for each Item # do to many warehouses.
Now, I need to pull the Qty Sold info. from Table2 into Table1 by matching Item # and warehouse for that Item #. I.e. Item # is in Column 1 and warehouse is in column 2 of Table 1 and same with Table2 the Item# is in Column1 and warehouse # is in column2 and Qty Sold is in Column 3.
Is this possible? Can you please help......
Thank you in advance.
Aman
Hi, all,
I need help!
I have Table1 and Table2 in Spreadsheet. In Table2, there are two or more Invoice Number under same Serial Number.
What I wnat is display the Invoice Number as Original Invoice Number in Table1 if Invoice Number in Table2 is different than Invoice Number in Table1 under same Serial Number.
I tried using Vlookup, but it just display the first value found.
Sample data is attached.
Please advise.
I need help!
I have Table1 and Table2 in Spreadsheet. In Table2, there are two or more Invoice Number under same Serial Number.
What I wnat is display the Invoice Number as Original Invoice Number in Table1 if Invoice Number in Table2 is different than Invoice Number in Table1 under same Serial Number.
I tried using Vlookup, but it just display the first value found.
Sample data is attached.
Please advise.