Is this an Index/Match formula?
Paul,
A point to note if you use multiple columns in the range being tested in
SUMPRODUCT, don't use the double unary form
(--(rng1=condition2),--(-rng2=condition2)), use the multiplication operator.
--
HTH
Bob
"Paul C" wrote in message
...
Just a quick note
Bob's method is the better of the two, Offset is a volitile function and
can
bog down large sheets with calculations.
I did not even think to use SUMPRODUCT with a condition across two
columns.
I use conditional SUMPRODUCT all the time, but my conditions are limited
to
individual columns. A very useful trick to remember. I can go home now,
I
learned something today.
--
If this helps, please remember to click yes.
"eflip" wrote:
Thank you both Paul and Bob - both formulas worked perfectly!
Thanks for the quick response as well.
"Bob Phillips" wrote:
Try this
=IF(SUMPRODUCT((Sheet1!$A$2:$A$20=A2)*(Sheet1!$B$2 :$C$20=B2)),"Qualifying",
IF(SUMPRODUCT((Sheet1!$A$2:$A$20=A2)*(Sheet1!$D$2: $E$20=B2)),"Non-qualifying",""))
--
HTH
Bob
"eflip" wrote in message
...
Hello -
I have 2 tabs of data.
The first tab has info by product and its qualifying vendors and
non-qualifying vendors (the vendors are in seperate columns)
ColumnA ColumnB ColumnsC ColumnD ColumnE
Product X Vendor1 Vendor2 Vendor3 Vendor4
Column A will always be unique
Columns B and C are qualifying vendors and Column D and E are
non-qualifying.
In the second tab I have the following:
ColumnA ColumnB
Product Vendor
What formula do I use to tell me if the vendor is Qualifying (Q) or
Non-Qualifying (N) or blank if the vendor is not included in the
list.
Any help would be greatly appreciated!
Thank you in advance.
.
|