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.

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.

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.

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