VLookup

[Kimberly]

I want to know how to do a VLookUp (I think that is the formula, I would you
use) with multiple criteria. For example:

If the supplier is George O'Days and the lot # is 10100, then I would want
the corresponding new and recon. value's inserted in the appropriate columns.

If and If Then 1 Then 2
Supplier Lot # Value New Value ReCon.
George Oday 10100 $149.00 $91.25


-Thank you, Kimberly

 

[John C]

I am assuming you have a table somewhere where all your data is stored. I
will name this data storage tab as Data, and assume the actual values begin
in row 2, and across columns A thru D.

Now, say I have a cell on my other tab that I have a Supplier listed along
next to a Lot Number
Value New:
=SUMPRODUCT(--(Data!$A$2:$A$100=supplier),--(Data!$B$2:$B$100=lotnumber),($C$2:$C$100))
Value ReCon:
==SUMPRODUCT(--(Data!$A$2:$A$100=supplier),--(Data!$B$2:$B$100=lotnumber),($D$2:$D$100))

 

[J Sedoff comRemove>]

For a single criteria, Vlookup is the way to go. However, with multiple
criteria, I would recommend SumProduct().

=SUMPRODUCT(--($A$2:$A$3="George Oday"),--($B$2:$B$3="10100"),C$2:C$3)
using the double negative (--) before each condition makes it a 1 or 0 for
true/false comparision.. in the current format, it looks at your table (rows
2 and 3) and checks if George Oday is in Column A, and 10100 is in Column B,
from there it sums whatever is in Column C.

SumProduct was confusing the first time I used it, so if you have any
followup questions, I'd be glad to answer them (although I know there is a
lot of answers in these newsgroups already, you might be able to find more
faster answers there!).

Hope this helps, Jim