VLOOKUP against two values

[wayneryeven]

Hey guys
I need to get a lookup against 2 values:

e.g.
Table1
A B C
Row 1 Model1 01/01/09

Table2
Model1 01/01/09 Passed

What i want to do is in Cell C, Table 1 do a lookup using Cells A and B. This will look up into Table 2 and retrieve the value Passed.

Im sure this is achievable in Excel, as far as design goes, without an indepth explanation and elaboration of the project this is how its constructed for future use.

Thus i need to do something like:
=VLOOKUP(A2 AND B2,STATUS,3,0)

Thanks guys for all your help,

 

[SkipVought]

Wayner,

Please use TGML tag TT when you post examples so that there is no ambiguity, what data is in what column. If you dont know what that is, do a FIND in this page on TGML and look for the TT tag.

You want to do something like this...
[tt]
=VLOOKUP(A2 AND B2,STATUS,3,0)
[/tt]
How about this...
[tt]
=IF(sumproduct((Sheet2!$A$2:$A$999=a2)*(Sheet2!$b$2:$b$999=c2)*(Sheet2!$c$2:$c$999="Passed"),"Passed","")
[/tt]
This may work if you have a reasonable and bounded number of status values. Otherwise a different solution may be necessary.




Skip,
^{Don't let the Diatribe...
talk you to death!}
_{Just traded in my old subtlety...
for a NUANCE!}

 

[AnotherHiggins]

You can't easily use VLookup for this. You'd have to add a helper column to your table and concatenate A2&B2 in the formula.

Better to use a different tool.

Have a look at SumProduct. I know, I know, it doesn't sound like it would do what you want, but it will.

It'll look something like:

[tab]=SumProduct((D1100 = A2) * (E1:E100 = B2) * (F1:F100))

that would return the value from column F where column D equals A2 and column E equals B2.

NOTE: You can't refer to an entire column in SumProduct.

[tt][blue]-John[/blue][/tt]
[tab][red]The plural of anecdote is not data[/red]

Help us help you. Please read FAQ 181-2886 before posting.