Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Vlookup w/multiple lookups
Value 1 Value 2 Value 3
1 1 A 1 2 B 2 1 C 2 2 D I want to lookup value 3, but I want use both value 1 & 2 before. What's the best way to accomplish this. |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Vlookup w/multiple lookups
You could simply rearrange your data.
A1 = A B1 = 1 C1 = 1 A2 = B B2 = 1 C2 = 2 A3 = C B3 = 2 C3 = 1 A4 = D B4 = 2 C4 = 2 A5 = the value you're looking for: A, B, C, D =VLOOKUP(A5,A1:C4,2,FALSE) returns 2 if A5 is a C =VLOOKUP(A5,A1:C4,3,FALSE) returns 1 if A5 is a C Tyro "JT" wrote in message ... Value 1 Value 2 Value 3 1 1 A 1 2 B 2 1 C 2 2 D I want to lookup value 3, but I want use both value 1 & 2 before. What's the best way to accomplish this. |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Vlookup w/multiple lookups
JT,
Is this your data table or your lookup table? I would add a column to your lookup table on the left side as a new first column. Enter a formula or in this first column that concatenates Value 1 & Value 2 together. In your example, I'll assume that is your lookup table and it starts in A1 (column labels/headers in first row...data starts in A2). 1. Insert a new column before column A, pushing your data to the right one column. 2. Enter one of the following formulas in A2: =B2 & C2 ...or... =B2 & "-" & C2 3. Copy/fill formula down to the last row of your lookup table. Now for your Vlookup formulas. Using your example again, but this time I'm gonna assume this is the data table, starts in A1, column headers in first row, data starts in A2 (Since I'm essentially using the same range for lookup table and data table, I'll use an ambiguous range reference for the lookup table reference so it doesn't end up being a circular reference). 1. In cell C2, enter one of the following formulas (depending on which concatenation formula you used above): =VLOOKUP(A2 & B2,{lookup table range},4,0) ...or... =VLOOKUP(A2 & "-" & B2,{lookup table range},4,0) 2. Copy/fill formula down (replace "{lookup table range}" appropriately) (if your lookup table range reference is just cell references, make sure they are absolute references. If it is a named reference, you shouldn't have a problem) HTH, Conan "JT" wrote in message ... Value 1 Value 2 Value 3 1 1 A 1 2 B 2 1 C 2 2 D I want to lookup value 3, but I want use both value 1 & 2 before. What's the best way to accomplish this. |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Vlookup w/multiple lookups
Saved from a previous post:
If you want exact matches for just two columns (and return a value from a third), you could use: =index(othersheet!$c$1:$c$100, match(1,(a2=othersheet!$a$1:$a$100) *(b2=othersheet!$b$1:$b$100),0)) (all in one cell) This is an array formula. Hit ctrl-shift-enter instead of enter. If you do it correctly, excel will wrap curly brackets {} around your formula. (don't type them yourself.) Adjust the range to match--but you can only use the whole column in xl2007. This returns the value in othersheet column C when column A and B (of othersheet) match A2 and B2 of the sheet with the formula. And you can add more conditions by just adding more stuff to that product portion of the formula: =index(othersheet!$d$1:$d$100, match(1,(a2=othersheet!$a$1:$a$100) *(b2=othersheet!$b$1:$b$100) *(c2=othersheet!$c$1:$c$100),0)) JT wrote: Value 1 Value 2 Value 3 1 1 A 1 2 B 2 1 C 2 2 D I want to lookup value 3, but I want use both value 1 & 2 before. What's the best way to accomplish this. -- Dave Peterson |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
sum multiple lookups? | Excel Worksheet Functions | |||
get a count from multiple lookups | Excel Worksheet Functions | |||
vlookup help with two of the same lookups | Excel Worksheet Functions | |||
Multiple V Lookups | Excel Worksheet Functions | |||
Multiple Lookups | Excel Discussion (Misc queries) |