|
Search Excel Forum Posts, Tutorials, Macros, Tips, and More
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
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 ...
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 ...
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:
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 B-D, 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 B-D 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
9-Jun
2
160
Jumbo
TC
*
*
*
2
0
50.9
0
2.5
0
0
3.5 x 42
*
0 PCS
4
9-Jun
3
160
Jumbo
Alpina
*
*
*
2
0
50.9
0
2.5
0
0
3.5 x 150
*
0 FT
Spreadsheet Formulas
Cell
Formula
C3
=A3-DATE(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
=A4-DATE(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
DM-1002
EVG
125
500
SS-7002
EVG
126
600
SO-MITB1
EVG
127
300
BUK-1005
BUK
128
300
BUK-1006
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'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 vise-versa.
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 vise-versa.
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
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.
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
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.
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
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
I have this cell formula:
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.
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 product-client-target 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 Table-2 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 table-array)?
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.
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 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:
=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 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!
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 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.
|
|
