VLOOKUP to search list for 3-cell match?

[alluknowiswrong]

Hi all,

I need help with a sales order form. On my Sheet2 I have a 4-colum
list for item #'s, item names, sizes, and costs. I need to be able t
type in the name, size, and cost in the B, C, & D columns and have th
item # appear in column A.

So basically, I need A1 to check B11 against the list on Sheet2 for
row that contains the same 3 values, and return the corresponding ite
#. Would this be a combination of VLOOKUP and IF or MATCH?

I started playing with this:

=IF(AND(B1=Sheet2!B1,C1=Sheet2!C1,D1=Sheet2!D1),Sheet2!A1)

but of course it doesn't check all rows in the list, which is what
need. Any suggestions?
________________________________
"It is not enough to teach,
one must punish as well."
- Peter Sellers, "The Magic Christian"

[/FONT

 

[DDM]

AllUKnow, here is the formula you need. Enter it in A2 on Sheet1 (assumes
you're using Row 1 for column headings on both sheets):

=SUMPRODUCT((Sheet2!A2:A20)*(Sheet2!B2:B20=B2)*(Sheet2!C2:C20=C2)*(Sheet2!D2
20=D2)).

Adjust cell ranges as necessary, of course.

DDM
"DDM's Microsoft Office Tips and Tricks"
www.ddmcomputing.com

 

[excelerator]

I am having a similar problem but need to look up if either one of tw
seperate conditions exist in the same column and then compare them to
seperate column for a third condition, which if it exists add it to th
total, example:

=SUMPRODUCT((RawData!E2:RawData!E27="A")*(RawData!E2:RawData!E27="B")*(RawData!K2:RawData!K27="SA"))

This doesn't work, anybody know a work around? Thanks

 

[Max]

One way, try instead:

=SUM(SUMPRODUCT((RawData!E2:E27="A")*(RawData!
K2:K27="SA")),SUMPRODUCT((RawData!E2:E27="B")*(RawData!
K2:K27="SA")))

 

[Harlan Grove]

One way, try instead:

=SUM(SUMPRODUCT((RawData!E2:E27="A")*(RawData!
K2:K27="SA")),SUMPRODUCT((RawData!E2:E27="B")*(RawData!
K2:K27="SA")))

....

Much easier & more efficient just to use

=SUMPRODUCT((RawData!E2:E27={"A","B"})*(RawData!K2:K27="SA"))

 

[Max]

Much easier & more efficient just to use
=SUMPRODUCT((RawData!E2:E27={"A","B"})*(RawData!

K2:K27="SA"))

Yes, you're right.
Thanks for the refinement, Harlan !

 

[alluknowiswrong]

Thanks DDM, but I can't get it to work

I think the problem may that I'm not trying to find the sum or total o
anything, just a match -if- the same three values on my first pag
match three values in the same row on the second page. Ideas anyone?


[/FONT

 

[Max]

Try this:

In Sheet2
-----------
Assume row1 is col headers, data from row2 down
(List for item #'s, item names, sizes, and costs in cols A to D)

Put in E2: =TRIM(B2&"_"&C2&"_"&D2)

Copy E2 down

In Sheet1
-----------
With name, size, and cost keyed into B1 to D1,
to extract the item# in A1

Put in A1:

=OFFSET(Sheet2!$A$1,MATCH(TRIM(B1&"_"&C1&"_"&D1),Sheet2!$E:$E,0)-1,0)

Alternatively, if you need an error trap to cater for non-matching cases

Put in A1:

=IF(ISNA(MATCH(TRIM(B1&"_"&C1&"_"&D1),Sheet2!$E:$E,0)),"No
match",OFFSET(Sheet2!$A$1,MATCH(TRIM(B1&"_"&C1&"_"&D1),Sheet2!$E:$E,0)-1,0))

The phrase "No match" will be returned for non-matching cases

--

Copy A1 down as many rows as you have data in cols B to D

 

[Max]

You're welcome !
Thanks for the feedback