
Search Excel Forum Posts, Tutorials, Macros, Tips, and More
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
Similar Excel Video Tutorials
Similar Topics
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
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 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?
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 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 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 am trying to setup a formula from cells that are set to a drop down list. On occasions I may need all the cells filled with a description to which I use a lookup with that adds to the lookup value in the cell below it and so on. However of 5 cells that I am adding lookup values toghether, certain occasions will require that only 2 of 5 cells be filled. When I do not select a description using the dropdown list and the cell is blank the formula does not work. Below is the code I started to write which works until a cell is entered that has nothing lookup, then things go south.
=((D14*VLOOKUP(H3,Table1,4,FALSE))*G3)/VLOOKUP(H3,Table1,3,FALSE)+((D14*VLOOKUP(H4,Table1,4,FALSE))*G4)/VLOOKUP(H4,Table1,3,FALSE)
You can see I am trying to add the results of several lookups together. I run into problems when I get to a cell that does not have anything in it I get the N/A response. What do I need to do so that it takes action when there is something to lookup and acts as 0 when the cell is empty????
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 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.
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
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
How do I write this as one formula.
if(C1=1,(Vlookup(D8,Table1,2,False),"") OR
if(C1=2,(Vlookup(D8,Table2,2,False),"") OR
if(C1=3,(Vlookup(D8,Table3,2,False),"").
I have spent so much time trying to come up with correct syntax or formula with no success. Thank you very much.
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
Is there a way to block the #N/A type stuff in a field until someone puts a value to lookup into it? Right now I'm entering stuff into the 2nd column and the surrounding columns are using it to look up their data, but as you can see its pretty messy until someone puts something in the 2nd column.
Is there some if statement or something I can use to make it just stay blank until someone enters a badge number into the 2nd column?
Roster
*
B
C
D
E
3
E14
283
Pierce
651
4
T13
248
Kissinger
645
5
#N/A
*
#N/A
#N/A
Spreadsheet Formulas
Cell
Formula
B3
=VLOOKUP(C3,vlookup!$A$2:$F$86,2,FALSE)
D3
=VLOOKUP(C3,vlookup!$A$2:$F$86,3,FALSE)
E3
=VLOOKUP(C3,vlookup!$A$2:$F$86,4,FALSE)
B4
=VLOOKUP(C4,vlookup!$A$2:$F$86,2,FALSE)
D4
=VLOOKUP(C4,vlookup!$A$2:$F$86,3,FALSE)
E4
=VLOOKUP(C4,vlookup!$A$2:$F$86,4,FALSE)
B5
=VLOOKUP(C5,vlookup!$A$2:$F$86,2,FALSE)
D5
=VLOOKUP(C5,vlookup!$A$2:$F$86,3,FALSE)
E5
=VLOOKUP(C5,vlookup!$A$2:$F$86,4,FALSE)
Excel tables to the web >> Excel Jeanie HTML 4
Thanks! btw this excel html jeanie thing is nice
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
I have a worksheet with the following formula
=IF(ISNA(VLOOKUP(G12,'F:\My Documents\PERep3&4.xls'!Table1,2,FALSE)),0,VLOOKUP(G12,'F:\My Documents\PERep3&4.xls'!Table1,2,FALSE))
I have tried using shift + F5 to replace the following, but cannot get it to work
1) 'F:\My Documents with 'c:\My Documents
2) PERep3&4 with liverep3&4
It would be appreciated if you could assist
Howard
Good day,
I hope that I am not asking too much with this one.
I have a workbook with 3 worksheets, 2 containing data and 1 used as a search and display sheet. I enter information into a cell, press enter and the relevant data is displayed in various cells.
I would like to create a userform to do the job of the search/display sheet, this is mainly to make it look more professional!
What do I need to add to the userform? Textboxes, Labels, Command Buttons etc, etc.
How would I go about transferring/copying the following data into a userform?
Thanks
Ak
A1
B1
C1
D1
E1
P1
=TODAY()
=NOW()
A3
=COUNT(Master!A4:A301)
Enter Product code below
P
N
M
E
F
S
W
S
S
G
R
A5
=VLOOKUP(C5,Master,7,FALSE)
=VLOOKUP(C5,Master,8,FALSE)
=VLOOKUP(C5,Master,9,FALSE)
=VLOOKUP(C5,Master,10,FALSE)
=VLOOKUP(C5,Master,11,FALSE)
=VLOOKUP(C5,Master,12,FALSE)
=VLOOKUP(C5,Master,13,FALSE)
=VLOOKUP(C5,Master,14,FALSE)
=VLOOKUP(C5,Master,15,FALSE)
=VLOOKUP(C5,Master,16,FALSE)
=VLOOKUP(C5,Master,17,FALSE)
B
M
O
M
C
M
G
H
L
S
P
A7
Product details
=VLOOKUP(C5,Master,18,FALSE)
=VLOOKUP(C5,Master,19,FALSE)
=VLOOKUP(C5,Master,20,FALSE)
=VLOOKUP(C5,Master,21,FALSE)
=VLOOKUP(C5,Master,22,FALSE)
=VLOOKUP(C5,Master,23,FALSE)
=VLOOKUP(C5,Master,24,FALSE)
=VLOOKUP(C5,Master,25,FALSE)
=VLOOKUP(C5,Master,26,FALSE)
=VLOOKUP(C5,Master,27,FALSE)
=VLOOKUP(C5,Master,28,FALSE)
Des
A9
=IF(ISNA(VLOOKUP(C5,Master,2,FALSE)),"Incorrect Product code",VLOOKUP(C5,Master,2,FALSE))
=VLOOKUP(C5,Master,29,FALSE)
=VLOOKUP(C5,Master,30,FALSE)
=VLOOKUP(C5,Master,31,FALSE)
=VLOOKUP(C5,Master,32,FALSE)
=VLOOKUP(C5,Master,33,FALSE)
=VLOOKUP(C5,Master,34,FALSE)
=VLOOKUP(C5,Master,35,FALSE)
=VLOOKUP(C5,Master,36,FALSE)
=VLOOKUP(C5,Master,37,FALSE)
=VLOOKUP(C5,Master,38,FALSE)
AL
=VLOOKUP(C5,Master,39,FALSE)
=VLOOKUP(C5,Master,40,FALSE)
=VLOOKUP(C5,Master,41,FALSE)
=VLOOKUP(C5,Master,42,FALSE)
=VLOOKUP(C5,Master,43,FALSE)
=VLOOKUP(C5,Master,44,FALSE)
=VLOOKUP(C5,Master,45,FALSE)
=VLOOKUP(C5,Master,46,FALSE)
=VLOOKUP(C5,Master,47,FALSE)
=VLOOKUP(C5,Master,48,FALSE)
A11
=VLOOKUP(C5,Master,3,FALSE)
=VLOOKUP(C5,Master,49,FALSE)
=VLOOKUP(C5,Master,50,FALSE)
=VLOOKUP(C5,Master,51,FALSE)
=VLOOKUP(C5,Master,52,FALSE)
=VLOOKUP(C5,Master,53,FALSE)
=VLOOKUP(C5,Master,54,FALSE)
=VLOOKUP(C5,Master,55,FALSE)
=VLOOKUP(C5,Master,56,FALSE)
=VLOOKUP(C5,Master,57,FALSE)
=VLOOKUP(C5,Master,58,FALSE)
Wa
=VLOOKUP(C5,Master,59,FALSE)
=VLOOKUP(C5,Master,60,FALSE)
=VLOOKUP(C5,Master,61,FALSE)
=VLOOKUP(C5,Master,62,FALSE)
=VLOOKUP(C5,Master,63,FALSE)
=VLOOKUP(C5,Master,64,FALSE)
=VLOOKUP(C5,Master,65,FALSE)
=VLOOKUP(C5,Master,66,FALSE)
=VLOOKUP(C5,Master,67,FALSE)
=VLOOKUP(C5,Master,68,FALSE)
A13
=VLOOKUP(C5,Master,4,FALSE)
=VLOOKUP(C5,Master,69,FALSE)
=VLOOKUP(C5,Master,70,FALSE)
=VLOOKUP(C5,Master,71,FALSE)
=VLOOKUP(C5,Master,72,FALSE)
=VLOOKUP(C5,Master,73,FALSE)
=VLOOKUP(C5,Master,74,FALSE)
=VLOOKUP(C5,Master,75,FALSE)
=VLOOKUP(C5,Master,76,FALSE)
=VLOOKUP(C5,Master,77,FALSE)
=VLOOKUP(C5,Master,78,FALSE)
Ar
=VLOOKUP(C5,Master,79,FALSE)
=VLOOKUP(C5,Master,80,FALSE)
=VLOOKUP(C5,Master,81,FALSE)
=VLOOKUP(C5,Master,82,FALSE)
=VLOOKUP(C5,Master,83,FALSE)
=VLOOKUP(C5,Master,84,FALSE)
=VLOOKUP(C5,Master,85,FALSE)
=VLOOKUP(C5,Master,86,FALSE)
=VLOOKUP(C5,Master,87,FALSE)
=VLOOKUP(C5,Master,88,FALSE)
A15
=VLOOKUP(C5,Master,5,FALSE)
=VLOOKUP(C5,Master,89,FALSE)
=VLOOKUP(C5,Master,90,FALSE)
=VLOOKUP(C5,Master,91,FALSE)
=VLOOKUP(C5,Master,92,FALSE)
=VLOOKUP(C5,Master,93,FALSE)
=VLOOKUP(C5,Master,94,FALSE)
=VLOOKUP(C5,Master,95,FALSE)
A17
=COUNTA(Ingredients!A4:A200)
=COUNTIF(Master!C4:C302,"Yes")
Enter Part code below
=(C19)
Comprises
A19
=VLOOKUP(C19,ing,3,FALSE)
=VLOOKUP(C19,ing,4,FALSE)
=VLOOKUP(C19,ing,5,FALSE)
=VLOOKUP(C19,ing,6,FALSE)
=VLOOKUP(C19,ing,7,FALSE)
Des
=VLOOKUP(C19,ing,8,FALSE)
=VLOOKUP(C19,ing,9,FALSE)
=VLOOKUP(C19,ing,10,FALSE)
=VLOOKUP(C19,ing,11,FALSE)
=VLOOKUP(C19,ing,12,FALSE)
A21
=IF(ISNA(VLOOKUP(C19,ing,2,FALSE)),"Incorrect Part code",VLOOKUP(C19,ing,2,FALSE))
=VLOOKUP(C19,ing,13,FALSE)
=VLOOKUP(C19,ing,14,FALSE)
=VLOOKUP(C19,ing,15,FALSE)
=VLOOKUP(C19,ing,16,FALSE)
=VLOOKUP(C19,ing,17,FALSE)
A23
E23
P23
Hi All,
I am trying to add two Time values together from an imported report. Both formats are in HH:MM:SS. However I get a value error when columns b does not go over a an hour i.e only has a MM:SS value. Does anyone know how to get around this.
Secondly is there anyway to show values that are over 24 hours in a sum, i.e. row 24 should be 25 hours, 5 mins and 41 seconds but shows as 02:05:41.
Collection
I
J
K
23
3:51:57
:23:45
#VALUE!
24
22:34:27
03:31:14
02:05:41
25
4:39:24
:11:43
#VALUE!
26
6:29:11
:18:32
#VALUE!
27
11:17:56
02:57:12
14:15:08
Spreadsheet Formulas
Cell
Formula
I23
=IF(ISNA (VLOOKUP (B23,'EWS 01'!A:N,4,FALSE) ) ,0,VLOOKUP (B23,'EWS 01'!A:N,4,FALSE) )
J23
=IF(ISNA (VLOOKUP (B23,'EWS 01'!A:N,6,FALSE) ) ,0,VLOOKUP (B23,'EWS 01'!A:N,6,FALSE) )
K23
=J23+I23
I24
=IF(ISNA (VLOOKUP (B24,'EWS 01'!A:N,4,FALSE) ) ,0,VLOOKUP (B24,'EWS 01'!A:N,4,FALSE) )
J24
=IF(ISNA (VLOOKUP (B24,'EWS 01'!A:N,6,FALSE) ) ,0,VLOOKUP (B24,'EWS 01'!A:N,6,FALSE) )
K24
=J24+I24
I25
=IF(ISNA (VLOOKUP (B25,'EWS 01'!A:N,4,FALSE) ) ,0,VLOOKUP (B25,'EWS 01'!A:N,4,FALSE) )
J25
=IF(ISNA (VLOOKUP (B25,'EWS 01'!A:N,6,FALSE) ) ,0,VLOOKUP (B25,'EWS 01'!A:N,6,FALSE) )
K25
=J25+I25
I26
=IF(ISNA (VLOOKUP (B26,'EWS 01'!A:N,4,FALSE) ) ,0,VLOOKUP (B26,'EWS 01'!A:N,4,FALSE) )
J26
=IF(ISNA (VLOOKUP (B26,'EWS 01'!A:N,6,FALSE) ) ,0,VLOOKUP (B26,'EWS 01'!A:N,6,FALSE) )
K26
=J26+I26
I27
=IF(ISNA (VLOOKUP (B27,'EWS 01'!A:N,4,FALSE) ) ,0,VLOOKUP (B27,'EWS 01'!A:N,4,FALSE) )
J27
=IF(ISNA (VLOOKUP (B27,'EWS 01'!A:N,6,FALSE) ) ,0,VLOOKUP (B27,'EWS 01'!A:N,6,FALSE) )
K27
=J27+I27
Excel tables to the web >> Excel Jeanie HTML 4
Below you'll see the long sheet. I highlighted the cells with issues. Seems my VLOOKUP is not pulling correct info. Help please.
Excel 2003
I
J
K
L
M
N
O
P
2
TRIP NUMBER IN
courier
3
GUA
UPS
Counts
Trips
Name
4
S27
UPS
FedEx
0
10
United
5
113
NOW
UPS
0
15
United
6
3
NOW
NOW
0
20
United
7
134
NOW
US MAIL
0
21
United
8
113
NOW
Prestige
0
23
United
9
316
NOW
#N/A
0
24
United
10
323
NOW
25
United
11
121
NOW
30
United
12
213
NOW
33
United
13
25
NOW
34
United
14
102
NOW
35
United
15
PAY
#N/A
36
United
16
528
NOW
101
NOW
17
109
NOW
102
NOW
18
UAM
UPS
103
NOW
19
47
NOW
104
NOW
20
AKR
Premier
105
NOW
21
UAM
UPS
106
NOW
22
UAM
UPS
107
NOW
23
S27
UPS
108
NOW
24
30A
NOW
109
NOW
25
25A
NOW
110
NOW
26
AKR
Premier
111
NOW
27
48
NOW
112
NOW
28
E26
USCARGO
113
NOW
29
FPM
FedEx
114
NOW
30
CLE
Premier
115
NOW
31
20
NOW
12
USMAIL
32
30A
NOW
120
NOW
33
15
NOW
121
NOW
34
25A
NOW
122
NOW
35
CLE
Premier
123
NOW
36
34
NOW
124
NOW
37
44
NOW
125
NOW
38
33
NOW
126
NOW
39
24
NOW
127
NOW
40
CLE
Premier
130
NOW
41
24
NOW
131
NOW
42
33
NOW
134
NOW
43
CLE
Premier
135
NOW
44
FAM
FedEx
136
NOW
45
FAM
FedEx
137
NOW
46
UAM
UPS
138
NOW
47
30A
NOW
139
NOW
48
S27
UPS
140
NOW
49
FAM
FedEx
143
NOW
50
CLE
Premier
144
NOW
51
S27
UPS
145
NOW
52
FAM
FedEx
15A
United
53
323
NOW
202
NOW
54
COL
Premier
203
NOW
55
CLE
Premier
204
NOW
56
CLE
Premier
205
NOW
57
GUA
UPS
206
NOW
58
106
NOW
207
NOW
59
MUP
#N/A
208
NOW
60
CLE
Premier
209
NOW
61
PAY
#N/A
210
NOW
62
CLE
Premier
211
NOW
63
CLE
Premier
212
NOW
64
AKR
Premier
213
NOW
65
214
NOW
214
NOW
66
PAY
#N/A
21A
United
67
PAY
#N/A
25A
United
68
FAM
FedEx
26
USCARGO
69
GUA
UPS
301
NOW
70
CLE
Premier
302
NOW
71
CLE
Premier
303
NOW
72
FAM
FedEx
304
NOW
73
23
NOW
305
NOW
74
CLE
Premier
306
NOW
75
COL
Premier
307
NOW
76
S27
UPS
309
NOW
77
CLE
Premier
30A
United
78
COL
Premier
310
NOW
Oct2010
Worksheet Formulas
Cell
Formula
J3
=VLOOKUP( LEFT( I3 )&"*",$O$3:$P$104,2,FALSE )
J4
=VLOOKUP( LEFT( I4 )&"*",$O$3:$P$104,2,FALSE )
J5
=VLOOKUP( LEFT( I5 )&"*",$O$3:$P$104,2,FALSE )
J6
=VLOOKUP( LEFT( I6 )&"*",$O$3:$P$104,2,FALSE )
J7
=VLOOKUP( LEFT( I7 )&"*",$O$3:$P$104,2,FALSE )
J8
=VLOOKUP( LEFT( I8 )&"*",$O$3:$P$104,2,FALSE )
J9
=VLOOKUP( LEFT( I9 )&"*",$O$3:$P$104,2,FALSE )
J10
=VLOOKUP( LEFT( I10 )&"*",$O$3:$P$104,2,FALSE )
J11
=VLOOKUP( LEFT( I11 )&"*",$O$3:$P$104,2,FALSE )
J12
=VLOOKUP( LEFT( I12 )&"*",$O$3:$P$104,2,FALSE )
J13
=VLOOKUP( LEFT( I13 )&"*",$O$3:$P$104,2,FALSE )
J14
=VLOOKUP( LEFT( I14 )&"*",$O$3:$P$104,2,FALSE )
J15
=VLOOKUP( LEFT( I15 )&"*",$O$3:$P$104,2,FALSE )
J16
=VLOOKUP( LEFT( I16 )&"*",$O$3:$P$104,2,FALSE )
J17
=VLOOKUP( LEFT( I17 )&"*",$O$3:$P$104,2,FALSE )
J18
=VLOOKUP( LEFT( I18 )&"*",$O$3:$P$104,2,FALSE )
J19
=VLOOKUP( LEFT( I19 )&"*",$O$3:$P$104,2,FALSE )
J20
=VLOOKUP( LEFT( I20 )&"*",$O$3:$P$104,2,FALSE )
J21
=VLOOKUP( LEFT( I21 )&"*",$O$3:$P$104,2,FALSE )
J22
=VLOOKUP( LEFT( I22 )&"*",$O$3:$P$104,2,FALSE )
J23
=VLOOKUP( LEFT( I23 )&"*",$O$3:$P$104,2,FALSE )
J24
=VLOOKUP( LEFT( I24 )&"*",$O$3:$P$104,2,FALSE )
J25
=VLOOKUP( LEFT( I25 )&"*",$O$3:$P$104,2,FALSE )
J26
=VLOOKUP( LEFT( I26 )&"*",$O$3:$P$104,2,FALSE )
J27
=VLOOKUP( LEFT( I27 )&"*",$O$3:$P$104,2,FALSE )
J28
=VLOOKUP( LEFT( I28 )&"*",$O$3:$P$104,2,FALSE )
J29
=VLOOKUP( LEFT( I29 )&"*",$O$3:$P$104,2,FALSE )
J30
=VLOOKUP( LEFT( I30 )&"*",$O$3:$P$104,2,FALSE )
J31
=VLOOKUP( LEFT( I31 )&"*",$O$3:$P$104,2,FALSE )
J32
=VLOOKUP( LEFT( I32 )&"*",$O$3:$P$104,2,FALSE )
J33
=VLOOKUP( LEFT( I33 )&"*",$O$3:$P$104,2,FALSE )
J34
=VLOOKUP( LEFT( I34 )&"*",$O$3:$P$104,2,FALSE )
J35
=VLOOKUP( LEFT( I35 )&"*",$O$3:$P$104,2,FALSE )
J36
=VLOOKUP( LEFT( I36 )&"*",$O$3:$P$104,2,FALSE )
J37
=VLOOKUP( LEFT( I37 )&"*",$O$3:$P$104,2,FALSE )
J38
=VLOOKUP( LEFT( I38 )&"*",$O$3:$P$104,2,FALSE )
J39
=VLOOKUP( LEFT( I39 )&"*",$O$3:$P$104,2,FALSE )
J40
=VLOOKUP( LEFT( I40 )&"*",$O$3:$P$104,2,FALSE )
J41
=VLOOKUP( LEFT( I41 )&"*",$O$3:$P$104,2,FALSE )
J42
=VLOOKUP( LEFT( I42 )&"*",$O$3:$P$104,2,FALSE )
J43
=VLOOKUP( LEFT( I43 )&"*",$O$3:$P$104,2,FALSE )
J44
=VLOOKUP( LEFT( I44 )&"*",$O$3:$P$104,2,FALSE )
J45
=VLOOKUP( LEFT( I45 )&"*",$O$3:$P$104,2,FALSE )
J46
=VLOOKUP( LEFT( I46 )&"*",$O$3:$P$104,2,FALSE )
J47
=VLOOKUP( LEFT( I47 )&"*",$O$3:$P$104,2,FALSE )
J48
=VLOOKUP( LEFT( I48 )&"*",$O$3:$P$104,2,FALSE )
J49
=VLOOKUP( LEFT( I49 )&"*",$O$3:$P$104,2,FALSE )
J50
=VLOOKUP( LEFT( I50 )&"*",$O$3:$P$104,2,FALSE )
J51
=VLOOKUP( LEFT( I51 )&"*",$O$3:$P$104,2,FALSE )
J52
=VLOOKUP( LEFT( I52 )&"*",$O$3:$P$104,2,FALSE )
J53
=VLOOKUP( LEFT( I53 )&"*",$O$3:$P$104,2,FALSE )
J54
=VLOOKUP( LEFT( I54 )&"*",$O$3:$P$104,2,FALSE )
J55
=VLOOKUP( LEFT( I55 )&"*",$O$3:$P$104,2,FALSE )
J56
=VLOOKUP( LEFT( I56 )&"*",$O$3:$P$104,2,FALSE )
J57
=VLOOKUP( LEFT( I57 )&"*",$O$3:$P$104,2,FALSE )
J58
=VLOOKUP( LEFT( I58 )&"*",$O$3:$P$104,2,FALSE )
J59
=VLOOKUP( LEFT( I59 )&"*",$O$3:$P$104,2,FALSE )
J60
=VLOOKUP( LEFT( I60 )&"*",$O$3:$P$104,2,FALSE )
J61
=VLOOKUP( LEFT( I61 )&"*",$O$3:$P$104,2,FALSE )
J62
=VLOOKUP( LEFT( I62 )&"*",$O$3:$P$104,2,FALSE )
J63
=VLOOKUP( LEFT( I63 )&"*",$O$3:$P$104,2,FALSE )
J64
=VLOOKUP( LEFT( I64 )&"*",$O$3:$P$104,2,FALSE )
J65
=VLOOKUP( LEFT( I65 )&"*",$O$3:$P$104,2,FALSE )
J66
=VLOOKUP( LEFT( I66 )&"*",$O$3:$P$104,2,FALSE )
J67
=VLOOKUP( LEFT( I67 )&"*",$O$3:$P$104,2,FALSE )
J68
=VLOOKUP( LEFT( I68 )&"*",$O$3:$P$104,2,FALSE )
J69
=VLOOKUP( LEFT( I69 )&"*",$O$3:$P$104,2,FALSE )
J70
=VLOOKUP( LEFT( I70 )&"*",$O$3:$P$104,2,FALSE )
J71
=VLOOKUP( LEFT( I71 )&"*",$O$3:$P$104,2,FALSE )
J72
=VLOOKUP( LEFT( I72 )&"*",$O$3:$P$104,2,FALSE )
J73
=VLOOKUP( LEFT( I73 )&"*",$O$3:$P$104,2,FALSE )
J74
=VLOOKUP( LEFT( I74 )&"*",$O$3:$P$104,2,FALSE )
J75
=VLOOKUP( LEFT( I75 )&"*",$O$3:$P$104,2,FALSE )
J76
=VLOOKUP( LEFT( I76 )&"*",$O$3:$P$104,2,FALSE )
J77
=VLOOKUP( LEFT( I77 )&"*",$O$3:$P$104,2,FALSE )
J78
=VLOOKUP( LEFT( I78 )&"*",$O$3:$P$104,2,FALSE )
Hello,
I am new to excel and am struggling with vlookup. I have looked at various posts regarding vlookup but couldn't successfully use it to find a solution to my problem. The below is the description of my problem. I have a table (table1) with material numbers which have a price . This value is time dependent i.e., a material 999 could have a price of $10 for 1/1/20081/15/2008 and $20 for 1/16/1008  1/31/2008.
A B C D
999 1/1/2008 1/15/2008 $10
999 1/15/2008 1/31/2008 $20
998 2/1/2008  2/25/2008 $15
I have another table (table2) in another sheet in the same workbook have a material and date.
A B C
999 1/10/2008
999 1/20/2008
998 2/15/2008
My requirement to take the material value and date in table2 and match it with table1 and get the value of column D in table 1 to column C of table2.
I have tried using vlookup but it only works for the first match and doesn't check for other values
below is the function that i tried
=if(and(vlookup(A2,Sheet2!A1:D4,2,false)<=Sheet1!B2,vlookup(Sheet1!A2,Sheet2!A2:D4,3,false)>=Sheet1! C2)),vlookup(Sheet1!A2,Sheet2!A2:D4,4,false),"error")
Can anyone please help me with this problem.
I apologize for the double post but I may not have been clear in my earlier one. I also wanted to include an example.
When using time values, formatted as Custom>h:mm, in a vlookup statement I get a not available error. If I type in the value or copy and paste it in the vlookup will work. I am not sure if it is the function I am using to subtract one minute or it is a formatting error.
This is what it looks like (missing in the formulas is D2 =A2):
Sheet1
A
B
C
D
E
1
Time
Price ($)
Time
Price ($)
2
22:44
49.66
22:44
49.66
3
22:43
50.66
22:43
#N/A
4
22:41
52.05
22:42
#N/A
5
22:35
52.07
22:41
#N/A
6
22:33
54.06
22:40
#N/A
7
22:29
54.43
22:39
#N/A
8
22:18
69.66
22:38
#N/A
9
22:00
73.01
22:37
#N/A
10
21:47
73.01
22:36
#N/A
11
21:41
75.82
22:35
#N/A
12
21:00
83.9
22:34
#N/A
13
20:18
75.82
22:33
#N/A
14
20:03
73.01
22:32
#N/A
15
20:00
69.66
22:31
#N/A
16
19:22
69.66
22:30
#N/A
17
19:00
73.01
22:29
#N/A
Spreadsheet Formulas
Cell
Formula
E2
=VLOOKUP(D2,$A$2:$B$17,2,FALSE)
D3
=D2TIME(0,1,0)
E3
=VLOOKUP(D3,$A$2:$B$17,2,FALSE)
D4
=D3TIME(0,1,0)
E4
=VLOOKUP(D4,$A$2:$B$17,2,FALSE)
D5
=D4TIME(0,1,0)
E5
=VLOOKUP(D5,$A$2:$B$17,2,FALSE)
D6
=D5TIME(0,1,0)
E6
=VLOOKUP(D6,$A$2:$B$17,2,FALSE)
D7
=D6TIME(0,1,0)
E7
=VLOOKUP(D7,$A$2:$B$17,2,FALSE)
D8
=D7TIME(0,1,0)
E8
=VLOOKUP(D8,$A$2:$B$17,2,FALSE)
D9
=D8TIME(0,1,0)
E9
=VLOOKUP(D9,$A$2:$B$17,2,FALSE)
D10
=D9TIME(0,1,0)
E10
=VLOOKUP(D10,$A$2:$B$17,2,FALSE)
D11
=D10TIME(0,1,0)
E11
=VLOOKUP(D11,$A$2:$B$17,2,FALSE)
D12
=D11TIME(0,1,0)
E12
=VLOOKUP(D12,$A$2:$B$17,2,FALSE)
D13
=D12TIME(0,1,0)
E13
=VLOOKUP(D13,$A$2:$B$17,2,FALSE)
D14
=D13TIME(0,1,0)
E14
=VLOOKUP(D14,$A$2:$B$17,2,FALSE)
D15
=D14TIME(0,1,0)
E15
=VLOOKUP(D15,$A$2:$B$17,2,FALSE)
D16
=D15TIME(0,1,0)
E16
=VLOOKUP(D16,$A$2:$B$17,2,FALSE)
D17
=D16TIME(0,1,0)
E17
=VLOOKUP(D17,$A$2:$B$17,2,FALSE)
Thank you.
Hello everyone, its been a while since my last post but after searching the boards for hours, I give up and need to ask for some help. Thanks to all who have posted and answered, its always a great help! I am sorry if I dont fully explain myself, I may be a little "punch drunk" fm searching for so long..... here is my delima.....
I have created a spreadsheet where I enter the date and time into cell A3 (formatted as ddmmmyy, hh:mm). In cell A4, i have created a formula that uses a vlookup to search "table1" for the local time zone conversion factor. My formula reads "=A3VLOOKUP(A1,Table1,4,FALSE). My formula works great, as long as you subtract the vlookup value fm A3. As I travel across timezones, eventually I will need to add time to A3.
How can I write this formula to return the local time, weather it is GMT 7:00 or GMT +4:00, without going in and actually changing the formula to read "=A3+VLOOKUP(A1,Table1,4,FALSE)? I need it to search the table to find out weather it should add 7 hrs, or subtract 8 hrs (depending on the timezone I am in). Is there a better way to create table1 so that when it looks it up, it will "just know" to add/subtract x hrs fm the time entered?
Currently I am still a little unsure about which time format to use when entering the station time (i.e. New York GMT 4:00; do I use hh:mm, [h]:mm, general, etc?), but I want the dispayed results to be displayed juat as I entered the original time (ddmmmyy, hh:mm).
ANY help to get me headed in the right direction would be great. Thanks again for the posts!
Mark
I am TRYING to write a formula that incorporates "VLOOKUP" and "RIGHT"  and can't seem to get it right. Below is what I have:
(VLOOKUP (c2,TABLE1,(RIGHT(5,4)),FALSE)
Where I am trying to pull the 5th column from a table defined, and get only the last 4 digits.
Can anyone help?
THANKS
The fomula is a mix of VLOOKUP and SEARCH:
=VSEARCH(A1;table1!A:A;2;false)
In the A1 is the Text “Shell GmbH”
In the A2 is the Text “Deutsche Shell”
In the A3 is the Text “Shell LTD.”
In Table1 Colum A it says Shell
In Table1 Colum B it says Shell Group
The result would be that the formula would give back always “Shell Group”.
The option “true” would even accept “Shel” or “Schell”
Thanks,
Eckhard
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 this query where I have two items("t51" or "041") in a field to not equal plus equal another field "type" to equal "pr", but it pulls all data instead of excluding. What am I doing wrong? below is my SQL
SELECT Table1.case, Table1.file, Sum(Table1.amount) AS SumOfamount
FROM Table1
GROUP BY Table1.case, Table1.file
HAVING (((Table1.case)"041") AND ((Table1.file)="pr")) OR (((Table1.case)"t51") AND ((Table1.file)="pr"));

