Is this an Index/Match formula?

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.

Use the Offset function to establish your range like this (I assume you would
have some kind of header in Row 1


Sheet 1
Row 1 ColumnA ColumnB ColumnsC ColumnD ColumnE
Row 2 Product X Vendor1 Vendor2 Vendor3 Vendor4

Sheet 2
Row 1 ColumnA ColumnB
Row 2 Product Vendor


OFFSET(Sheet1!A1,match(A2,Sheet1!$A$2:$A$10,0),1,1 ,4)
This establishes a range starting match(A2,Sheet1!$A$2:$A$10,0) rows from A1
and 1 column over. The range is 1 row in height and 4 columns in width)
then use this for your match

Match(B2,OFFSET(Sheet1!A1,match(A2,Sheet1!$A$2:$A$ 10,0),1,1,4),0)

Finally set your Qualifying conditions with an IF

=IF(Match(B2,OFFSET(Sheet1!A1,match(A2,Sheet1!$A$2 :$A$10,0),1,1,4),0)2,"Non
Qualifying","Qualifying")

This formula goes in C2 on Sheet 2
--
If this helps, please remember to click yes.


"eflip" wrote:

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.

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.

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.




.

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.




.

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.




.