
Search Excel Forum Posts, Tutorials, Macros, Tips, and More
Hi,
I have a vlookup where the table_array is the variable part..How do i do it?
For example.
VLOOKUP(B1, XXX, 3,FALSE)
So in the formula XXX is the variable part..but i am not able to do it..Can anyone help in this regard...Basically, XXX is either Table1 or Table2 or Table3 etc..but i am not able to figure this out..
Please help.
Satish
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 ...
IF AND vs. VLOOKUP
 See how to use the IF, AND and VLOOKUP functions to deliver the words Bid, No Bid, or Alert to a cell. Is VLOOKUP better than IF AND? ...
Similar Topics
Hey Guys I Have I Q
I'd Like To Make To Make Every Thing Variable In "Gray Table" So I Can Choose According To My Need I Work
Ex
1 Ranges Variables So I Can Choose The Range I Need
2 Look Up Table Variable So I Can Choose Between Tables I need
3 Color Every Range With Specific Color Through Drop Down List Color
Sheet1
A
B
C
D
E
F
G
H
I
J
K
L
M
N
O
P
Q
1
A1
C 700
Range
Tables
Color
Table1
Table2
Table3
2
A2
C 701
First LookUp
A1
A4
Table1
Blue Sky
A1
C 700
A1
Z 500
A1
Y 300
3
A3
C 702
Second LookUp
A5
A13
Table3
Red
A2
C 701
A2
Z 501
A2
Y 301
4
A4
C 703
Third LookUp
A14
A18
Table2
Yellow
A3
C 702
A3
Z 502
A3
Y 302
5
A5
Y 304
A4
C 703
A4
Z 503
A4
Y 303
6
A6
Y 305
A5
C 704
A5
Z 504
A5
Y 304
7
A7
Y 306
A6
C 705
A6
Z 505
A6
Y 305
8
A8
Y 307
A7
C 706
A7
Z 506
A7
Y 306
9
A9
Y 308
A8
C 707
A8
Z 507
A8
Y 307
10
A10
Y 309
A9
C 708
A9
Z 508
A9
Y 308
11
A11
Y 310
A10
C 709
A10
Z 509
A10
Y 309
12
A12
Y 311
A11
C 710
A11
Z 510
A11
Y 310
13
A13
Y 312
A12
C 711
A12
Z 511
A12
Y 311
14
A14
Z 513
A13
C 712
A13
Z 512
A13
Y 312
15
A15
Z 514
A14
C 713
A14
Z 513
A14
Y 313
16
A16
Z 515
A15
C 714
A15
Z 514
A15
Y 314
17
A17
Z 516
A16
C 715
A16
Z 515
A16
Y 315
18
A18
Z 517
A17
C 716
A17
Z 516
A17
Y 316
19
A18
C 717
A18
Z 517
A18
Y 317
20
A19
C 718
A19
Z 518
A19
Y 318
21
A20
C 719
A20
Z519
A20
Y 319
Spreadsheet Formulas
Cell
Formula
B1
=VLOOKUP(A1,Table1,2,0)
B2
=VLOOKUP(A2,Table1,2,0)
B3
=VLOOKUP(A3,Table1,2,0)
B4
=VLOOKUP(A4,Table1,2,0)
B5
=VLOOKUP(A5,Table3,2,0)
B6
=VLOOKUP(A6,Table3,2,0)
B7
=VLOOKUP(A7,Table3,2,0)
B8
=VLOOKUP(A8,Table3,2,0)
B9
=VLOOKUP(A9,Table3,2,0)
B10
=VLOOKUP(A10,Table3,2,0)
B11
=VLOOKUP(A11,Table3,2,0)
B12
=VLOOKUP(A12,Table3,2,0)
B13
=VLOOKUP(A13,Table3,2,0)
B14
=VLOOKUP(A14,Table2,2,0)
B15
=VLOOKUP(A15,Table2,2,0)
B16
=VLOOKUP(A16,Table2,2,0)
B17
=VLOOKUP(A17,Table2,2,0)
B18
=VLOOKUP(A18,Table2,2,0)
Excel tables to the web >> Excel Jeanie HTML 4
I'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'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?
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 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
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.
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
Is there a way to use a variable for the table_array in a VLOOKUP, specifically, where the table_array is in another workbook? I'm not afraid to use a VBA function if necessary but it'd be nice to have the function all 'selfcontained' using standard formula commands.
This is an example of what I'm looking to do:
Code:
=VLOOKUP(TEXT($A7,"###0"), CONCATENATE("SEASONALLY ADJUSTED.xls!MinMax",ClothingType), 22 + GenderOffset, FALSE)
Since a string is returned, this function fails. I'm guessing I need a Range returned?
The "CONCATENATE("..." part is the piece causing me the issue. Depending upon what Form (radio) button the user clicks (that part is done), the table_array range will change.
Suggestions?
Good day,
I hope that I am not asking too much with this one.
I have a workbook with 3 worksheets, 2 containing data and 1 used as a search and display sheet. I enter information into a cell, press enter and the relevant data is displayed in various cells.
I would like to create a userform to do the job of the search/display sheet, this is mainly to make it look more professional!
What do I need to add to the userform? Textboxes, Labels, Command Buttons etc, etc.
How would I go about transferring/copying the following data into a userform?
Thanks
Ak
A1
B1
C1
D1
E1
P1
=TODAY()
=NOW()
A3
=COUNT(Master!A4:A301)
Enter Product code below
P
N
M
E
F
S
W
S
S
G
R
A5
=VLOOKUP(C5,Master,7,FALSE)
=VLOOKUP(C5,Master,8,FALSE)
=VLOOKUP(C5,Master,9,FALSE)
=VLOOKUP(C5,Master,10,FALSE)
=VLOOKUP(C5,Master,11,FALSE)
=VLOOKUP(C5,Master,12,FALSE)
=VLOOKUP(C5,Master,13,FALSE)
=VLOOKUP(C5,Master,14,FALSE)
=VLOOKUP(C5,Master,15,FALSE)
=VLOOKUP(C5,Master,16,FALSE)
=VLOOKUP(C5,Master,17,FALSE)
B
M
O
M
C
M
G
H
L
S
P
A7
Product details
=VLOOKUP(C5,Master,18,FALSE)
=VLOOKUP(C5,Master,19,FALSE)
=VLOOKUP(C5,Master,20,FALSE)
=VLOOKUP(C5,Master,21,FALSE)
=VLOOKUP(C5,Master,22,FALSE)
=VLOOKUP(C5,Master,23,FALSE)
=VLOOKUP(C5,Master,24,FALSE)
=VLOOKUP(C5,Master,25,FALSE)
=VLOOKUP(C5,Master,26,FALSE)
=VLOOKUP(C5,Master,27,FALSE)
=VLOOKUP(C5,Master,28,FALSE)
Des
A9
=IF(ISNA(VLOOKUP(C5,Master,2,FALSE)),"Incorrect Product code",VLOOKUP(C5,Master,2,FALSE))
=VLOOKUP(C5,Master,29,FALSE)
=VLOOKUP(C5,Master,30,FALSE)
=VLOOKUP(C5,Master,31,FALSE)
=VLOOKUP(C5,Master,32,FALSE)
=VLOOKUP(C5,Master,33,FALSE)
=VLOOKUP(C5,Master,34,FALSE)
=VLOOKUP(C5,Master,35,FALSE)
=VLOOKUP(C5,Master,36,FALSE)
=VLOOKUP(C5,Master,37,FALSE)
=VLOOKUP(C5,Master,38,FALSE)
AL
=VLOOKUP(C5,Master,39,FALSE)
=VLOOKUP(C5,Master,40,FALSE)
=VLOOKUP(C5,Master,41,FALSE)
=VLOOKUP(C5,Master,42,FALSE)
=VLOOKUP(C5,Master,43,FALSE)
=VLOOKUP(C5,Master,44,FALSE)
=VLOOKUP(C5,Master,45,FALSE)
=VLOOKUP(C5,Master,46,FALSE)
=VLOOKUP(C5,Master,47,FALSE)
=VLOOKUP(C5,Master,48,FALSE)
A11
=VLOOKUP(C5,Master,3,FALSE)
=VLOOKUP(C5,Master,49,FALSE)
=VLOOKUP(C5,Master,50,FALSE)
=VLOOKUP(C5,Master,51,FALSE)
=VLOOKUP(C5,Master,52,FALSE)
=VLOOKUP(C5,Master,53,FALSE)
=VLOOKUP(C5,Master,54,FALSE)
=VLOOKUP(C5,Master,55,FALSE)
=VLOOKUP(C5,Master,56,FALSE)
=VLOOKUP(C5,Master,57,FALSE)
=VLOOKUP(C5,Master,58,FALSE)
Wa
=VLOOKUP(C5,Master,59,FALSE)
=VLOOKUP(C5,Master,60,FALSE)
=VLOOKUP(C5,Master,61,FALSE)
=VLOOKUP(C5,Master,62,FALSE)
=VLOOKUP(C5,Master,63,FALSE)
=VLOOKUP(C5,Master,64,FALSE)
=VLOOKUP(C5,Master,65,FALSE)
=VLOOKUP(C5,Master,66,FALSE)
=VLOOKUP(C5,Master,67,FALSE)
=VLOOKUP(C5,Master,68,FALSE)
A13
=VLOOKUP(C5,Master,4,FALSE)
=VLOOKUP(C5,Master,69,FALSE)
=VLOOKUP(C5,Master,70,FALSE)
=VLOOKUP(C5,Master,71,FALSE)
=VLOOKUP(C5,Master,72,FALSE)
=VLOOKUP(C5,Master,73,FALSE)
=VLOOKUP(C5,Master,74,FALSE)
=VLOOKUP(C5,Master,75,FALSE)
=VLOOKUP(C5,Master,76,FALSE)
=VLOOKUP(C5,Master,77,FALSE)
=VLOOKUP(C5,Master,78,FALSE)
Ar
=VLOOKUP(C5,Master,79,FALSE)
=VLOOKUP(C5,Master,80,FALSE)
=VLOOKUP(C5,Master,81,FALSE)
=VLOOKUP(C5,Master,82,FALSE)
=VLOOKUP(C5,Master,83,FALSE)
=VLOOKUP(C5,Master,84,FALSE)
=VLOOKUP(C5,Master,85,FALSE)
=VLOOKUP(C5,Master,86,FALSE)
=VLOOKUP(C5,Master,87,FALSE)
=VLOOKUP(C5,Master,88,FALSE)
A15
=VLOOKUP(C5,Master,5,FALSE)
=VLOOKUP(C5,Master,89,FALSE)
=VLOOKUP(C5,Master,90,FALSE)
=VLOOKUP(C5,Master,91,FALSE)
=VLOOKUP(C5,Master,92,FALSE)
=VLOOKUP(C5,Master,93,FALSE)
=VLOOKUP(C5,Master,94,FALSE)
=VLOOKUP(C5,Master,95,FALSE)
A17
=COUNTA(Ingredients!A4:A200)
=COUNTIF(Master!C4:C302,"Yes")
Enter Part code below
=(C19)
Comprises
A19
=VLOOKUP(C19,ing,3,FALSE)
=VLOOKUP(C19,ing,4,FALSE)
=VLOOKUP(C19,ing,5,FALSE)
=VLOOKUP(C19,ing,6,FALSE)
=VLOOKUP(C19,ing,7,FALSE)
Des
=VLOOKUP(C19,ing,8,FALSE)
=VLOOKUP(C19,ing,9,FALSE)
=VLOOKUP(C19,ing,10,FALSE)
=VLOOKUP(C19,ing,11,FALSE)
=VLOOKUP(C19,ing,12,FALSE)
A21
=IF(ISNA(VLOOKUP(C19,ing,2,FALSE)),"Incorrect Part code",VLOOKUP(C19,ing,2,FALSE))
=VLOOKUP(C19,ing,13,FALSE)
=VLOOKUP(C19,ing,14,FALSE)
=VLOOKUP(C19,ing,15,FALSE)
=VLOOKUP(C19,ing,16,FALSE)
=VLOOKUP(C19,ing,17,FALSE)
A23
E23
P23
I am looking to do a VLOOKUP where the 2nd part of the table_array is located before the first part. Whenever I write formula Excel changes it and gives me an error message. Basically I am trying to do...
=VLOOKUP(C10,D:A,3,FALSE)
I am not sure if you can use a 3 or not but Excel keeps switching the D & A changing the formula to...
=VLOOKUP(C10,A:D,3,FALSE)
Still giving me an error for the 3
If I can't do what I want with VLOOKUP is there another way?
Hi!
I have 3 tables, Table1 is in page 1.
Table2 and Table3 are in page 2.
Table2 gets a value from Table3 and Table1 gets a value from Table2.
However my problem is that no matter which values i input in Table3, it always show the value 0 in Table1.
If it matters, here are the formulas my tables use
Table1[Utgifter] = SUBTOTAL(109; Table2[Utgifter])
Table2[Utgifter] = SUBTOTAL(109; Table3[Utgifter])
Table3[Utgifter] = [a]  [b]
('a' and 'b' are row values in Table3).
So why do i always get the value 0 in Table1 and how can i fix this?
Thanks!
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
Hi,
I would like to use the Vlookup function but unsure of how to add a condition. Depending upon what is in one cell (a number between 1  9, which corresponds to a separate name range on a different worksheet (e.g. 1 = Rank1Opponent; 2 = Rank2Opponent...) affects which table_array is used in the lookup.
I've included the basic Vlookup, which works as it stands:
=VLOOKUP(A2, Rank1Opponent ,2,FALSE)
At the moment, the easiest thing for me to do is add additional columns with the Vlookup function referencing different name ranges (i.e. =VLOOKUP(A2,Rank2Opponent,2,FALSE) etc.) but I think this is probably a long winded way of doing it.
I thought about using nested IF statements but wondered if there was a more 'efficient' way of doing it?
Any advice would be greatly appreciated,
Cheers
Hi all,
I'm hoping this is just a quick and easy thing I'm missing but I cannot seem to figure out how to use a concatination within the table_array of a Vlookup.
So far I have
PHP Code:
=IF(ISNA(VLOOKUP(A2&H2&I2,Billed!AE:AE,1,FALSE)),"Not Billed","Billed")
What I am trying to do is replace the "Billed!AE:AE" part with "Billed!A2&H2&I2" but I am unsure if this is correct and if so what does the col_index_num become?
Thanks in advanced.
Guy
Hi folks
I am wondering is it possible to use a variable value for the table_array input on a vlookupargument.
So for instance the vlookup argument is
=vlookup(lookup_value, table_array, col_index_num, [range_lookup])
and I would like to have one of several arrays used depending what is selected in a drop down box.
Can this be done or could someone suggest a different way of doing it...
Thanks!
senan_g
I looked through several pages of VLOOKUP and couldn't find anything other that doing VBA MULTLOOKUP and was wondering if possible...
I have a part number on one worksheet that looks at another sheet for the same part number to return the yearly usage.
BUT, sometimes there are multiple customers on that sheet that use the same part number. Only the first result is returned.
So right now, it's
=VLOOKUP(C337,forecast!D:CW,98,FALSE)
Yes, I have lots of columns...
Any simple way of having the vlookup return the sum of the one PN from several rows in column 98?
Hi there,
I am trying to do a vlookup that currently looks as follows:
Code:
Range("C2").Formula = "=VLOOKUP(A2,SAP!A$2:AA$42,2,FALSE)"
However, the selection of the table_array (SAP!A$2:AA$42) is a set value. Instead I would like to select all the data that is on sheet SAP. The idea is that the data set will not always be A2:AA42, so I need to make this part variable.
Normally I would use
Code:
Range(Selection, Selection.End(xlDown)).Select
Range(Selection, Selection.End(xlToRight)).Select
to select the entire data set.
How do I combine these two?
Please note that I will be copying the the vlookup formula down for a bunch of cells.
regards,
Jay
My VBA is so rusty!! need help on the Findfirst syntax
ok I have two tables, table1 and table2. Both have a field called "CKey". Basically I am cycling through each record in Table1, to find the "CKey" in table2 and do something.
I have written:
table2.MoveFirst
Do While Not table2.EOF
Message2 = "Working on record " & counter & " of " & lastrec
DoEvents
strFindKey = table2!CKey
strCriteria = "Ckey='" & strFindKey & "'"
table3.FindFirst strCriteria
If Not table3.NoMatch Then....
I get this message "The Microsoft Jet Engine does not recognise "CKey" as an expression ....."
What's going wrong? Help!!
I have a spreadsheet that has a series of single level bill of materials. Column A is the part number, Column B is the bill of material level, Column C is the parent for the part number, column D is the lead time/processing time for the part (total time to order the part and assemble it into the parent). I am trying to calculate the total lead time for each part using a formula in column E. For an example, a level 3 part would add together its lead time, the lead time for its parent (level 2 part), and the lead time of the parent's parent (level 1 part). Note: we are not considering level 0 since assembling all of the subassemblies to it takes relatively no time.
If the parts were listed in order from parent to lowest level child or child to parent, I have seen solutions here for calculating BOM quantities that could be potentially adapted to sum lead times, but that is not the case here  the parts can be listed in any order. My current solution is not elegant whatsoever and only goes down to a level 5 part. It is a series of progessively more nested VLOOKUP terms that are summed:
=D2+IF(B2>1,VLOOKUP(C2,A$2:D$41,4,FALSE),0)+IF(B2>2,VLOOKUP(VLOOKUP(C2,A$2:C$41,3,FALSE),A$2:D$41,4,FALSE),0)+IF(B2>3,VLOOKUP(VLOOKUP(VLOOKUP(C2,A$2:C$41,3,FALSE),A$2:C$41,3,FALSE),A$2:D$41,4,FALSE),0)+IF(B2>4,VLOOKUP(VLOOKUP(VLOOKUP(VLOOKUP(C2,A$2:C$41,3,FALSE),A$2:C$41,3,FALSE),A$2:C$41,3,FALSE),A$2:D$41,4,FALSE),0)+IF(B2>5,VLOOKUP(VLOOKUP(VLOOKUP(VLOOKUP(VLOOKUP(C2,A$2:C$41,3,FALSE),A$2:C$41,3,FALSE),A$2:C$41,3,FALSE),A$2:C$41,3,FALSE),A$2 :D$41,4,FALSE),0)
If I end up having a level 6 part, I will need to add an even longer term onto the end of this formula and if I end up with a level 7 part at some future point, I think the formula will run too long to fit in a cell. Is there a better way of doing this? I have been racking my brain thinking that SUMPRODUCT can somehow come to the rescue, but have come up empty so far.
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'm having a problem getting my table_array to stay constant. It keeps going down one cell every time I try to CTRL+D the formula. The only variable I want to change in this VLOOKUP is the lookup_value. For example:
Here is my C2 Cell:
=VLOOKUP(A2, B2:C100,2,False)
And in C3 (When I try to CTRL + D) It will change to:
=VLOOKUP(A3,B3:C101,2,False)
But this is how I would like to get it to work(where the table array is constant):
=VLOOKUP(A3, B2:C100,2,False)
Any help would be much appreciated.
Hello, I am trying to "manually" enter the table_array in a VLOOKUP function. I basically want to be able to define the table_array using functions nested within the VLOOKUP.
For simplicity, I want to do a VLOOKUP over a range of let's say... A1:C3. I can use the following in a cell by itself to produce "A1:C3"
A B
x 1
y 3
=CONCATENATE("A",B2,":","C",B3)
and
=ADDRESS(1,B1,4)&":"&ADDRESS(3,B2,4)
These both return A1:C3 for me, but when I embed them in the argument of the VLOOKUP, I get a #VALUE error.
I have tried using TEXT() to convert the output into text, I have tried leaving the result A1:C3 in a cell and referencing that cell in the table_array argument of VLOOKUP... nothing seems to work.
=VLOOKUP(C21, ADDRESS(1,B1,4)&":"&ADDRESS(3,B2,4), 7)
Note: in the example above, C21 is the value I want to find in the first row and the 7th column contains the data I want to look up. If I do manually enter in the range A1:C3 in place of the ADDRESS functions, the formula works fine... so it has to do with calculating the table_array
I know it seems obvious that I should just type in A1:C3, but I want to be able to calculate the range based on other criteria and don't want to have to manually enter the range for each VLOOKUP.
Can anyone help?
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 have set up a vlookup table which feeds off lists in another worksheet. The vlookup formula is in the main table.
Prior to any additions the vlookup would enter a blank cell as "" (i.e.blank), however, after making additional entries the new additions are being reported as 0 (zero) in cells which do not (purposefully) include a variable.
I can't understand why this has happened and would prefer to have blank cells in those that have no variable.
The vlookup formulas a
=VLOOKUP($D7,VLookupList1,2,FALSE)
=VLOOKUP($D7,VLookupList1,3,FALSE)
=VLOOKUP($D7,VLookupList1,4,FALSE)
=VLOOKUP($D7,VLookupList1,5,FALSE)
Any ideas?

