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 Tutorials
Vlookup with a Range of Numbers in Excel
How to use the Vlookup function to return a result that falls within a range of numbers, such as a weight or quanti ...
How to use the Vlookup function to return a result that falls within a range of numbers, such as a weight or quanti ...
Remove Vlookup #N/A Error in Excel
How to remove the #N/A error from Vlookup and replace it with a friendly message or a blank cell. Fix the Vlookup ...
How to remove the #N/A error from Vlookup and replace it with a friendly message or a blank cell. Fix the Vlookup ...
Vlookup on Data with Spaces
How to use the Vlookup function when your data has extra spaces in it. This happens when the lookup data is import ...
How to use the Vlookup function when your data has extra spaces in it. This happens when the lookup data is import ...
How to use Vlookup Across Multiple Worksheets in Excel
This tutorial shows you how to use the Vlookup function across multiple worksheets within Excel. This means that y ...
This tutorial shows you how to use the Vlookup function across multiple worksheets within Excel. This means that y ...
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
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
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
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 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, 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
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.
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 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.
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.
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.
I am trying to do a vlookup which will pull in a customer sales from there own worksheet tab. On one sheet tab I have the customer #'s starting on A1 : A10 such as 101 ,110, 210etc. Each customer has there own worksheet tab which is labeled with there customer #. How can I do a vlookup to there customer # on cell A1:A10 and then find their tab with there customer #, and pull there data from their tab. Lets say on the customer tab the data is on A1:E1 and I'm pulling the number on E1 which is column index 5. I thank you always for your help.
I've got one w/s for each of my customers with part numbers, into which I need to enter quantities ordered last month. I've got another w/s that has all part numbers, customer, and qty ordered last month (in successive columns  a summary sheet). I have a VLOOKUP that pulls the qty from the second file into each individual customers' worksheet =VLOOKUP(A3,'[0907DETAIL .xls]DETAIL'!$B$2:$G$647,4,FALSE)  but, this sometimes pulls extra qty's into a customer's w/s (for ex., when both Customer A and Customer B can purchase a particular p/n, it will appear in each customer's template file...but when I run VLOOKUP, even if Customer A hasn't purchased the part last month, Customer B's qty will be pulled into Customer A's worksheet.
Basically I want to be able to nest an IF stmt of some sort into the VLOOKUP so that the formula only pays attn to items on the summary sheet with the correct Customer ID. I'd imagine I could do something like =IF('[0907DETAIL .xls]DETAIL'!$C$491:$C$647=YIL,VLOOKUP(A3,'[0907DETAIL .xls]DETAIL'!$B$2:$G$647,4,FALSE),"")  but that doesn't work. Column C is the Customer ID in the summary w/s, btw.
Basically I want to be able to nest an IF stmt of some sort into the VLOOKUP so that the formula only pays attn to items on the summary sheet with the correct Customer ID. I'd imagine I could do something like =IF('[0907DETAIL .xls]DETAIL'!$C$491:$C$647=YIL,VLOOKUP(A3,'[0907DETAIL .xls]DETAIL'!$B$2:$G$647,4,FALSE),"")  but that doesn't work. Column C is the Customer ID in the summary w/s, btw.
Hello,
I am trying to apply a COUNTIF function to only visible cells in a selected range. Here's the regular COUNTIF formula that I'm working with.
=COUNTIF(INDEX(TABLE1,0,MATCH($B8,TABLE1[#Headers],0)),VLOOKUP(18,$AF$7:$AG$28,2,FALSE))
Column B in the current sheet contains dates, which are also present in the header row in TABLE1. This countif formula does the following:
1) Uses INDEX(TABLE1,0,MATCH($B8,TABLE1[#Headers],0)) to define the range as the column in TABLE1 with a header that matches the date in $B8.
2) Uses VLOOKUP(18,$AF$7:$AG$28,2,FALSE) to define the search term through a VLOOKUP of a small table in the current sheet.
The formula works just fine. However, I'm unsure of how to get it to ignore hidden rows. I know that you can do this with simpler functions using SUBTOTAL, and I have a VBA solution that works but is extremely slow. Any ideas?
Thanks for your help.
Hi there
I want to set up a vlookup
=vlookup(A2,Table,2)
But, the range I look up I want to vary e.g.
= vlookup(A2,if(C1=1,D8,D9),2)
Where D8 = "Table1", D9 = "Table2"
and both tables are named ranges
Excel doesn't like this. How can I make this work?
I want to set up a vlookup
=vlookup(A2,Table,2)
But, the range I look up I want to vary e.g.
= vlookup(A2,if(C1=1,D8,D9),2)
Where D8 = "Table1", D9 = "Table2"
and both tables are named ranges
Excel doesn't like this. How can I make this work?
hello!
i am trying to transfer data from one table to another using vlookup function.
If the check box is ticked, this means that this invoice has been paid and returns a TRUE; otherwise, it remains blank or simply indicates a FALSE.
This is the table I am trying to populate to summarize all outstanding items. Currently I am using a vlookup function to do this. The problem is if an invoice for the quarter has been paid in Table1, i have to manually delete the formula in Table2 so as not to show it as outstanding.
=IF(ISNA(VLOOKUP(A4,Sheet1!A4:Q6,2,0)),0,(VLOOKUP(A4,Sheet1!A4:Q6,2,0)))
I know I can use the TRUE, FALSE and BLANK status in Table1 to make this work more efficient. However, when I tried to do a multiple vlookup function nested in IF, it's not picking any numbers at all.
I thought I'd consult your guidance on this. Any assistance you can extend is truly appreciated.
annais
i am trying to transfer data from one table to another using vlookup function.
If the check box is ticked, this means that this invoice has been paid and returns a TRUE; otherwise, it remains blank or simply indicates a FALSE.
This is the table I am trying to populate to summarize all outstanding items. Currently I am using a vlookup function to do this. The problem is if an invoice for the quarter has been paid in Table1, i have to manually delete the formula in Table2 so as not to show it as outstanding.
=IF(ISNA(VLOOKUP(A4,Sheet1!A4:Q6,2,0)),0,(VLOOKUP(A4,Sheet1!A4:Q6,2,0)))
I know I can use the TRUE, FALSE and BLANK status in Table1 to make this work more efficient. However, when I tried to do a multiple vlookup function nested in IF, it's not picking any numbers at all.
I thought I'd consult your guidance on this. Any assistance you can extend is truly appreciated.
annais