Prev: HLOOKUP into another xls file
Next: Question about syntax for conditional formatting (RESOLVED)
From: eflip on 22 Mar 2010 12:40
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.


From: Paul C on 22 Mar 2010 12:55
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.
>
>
From: Bob Phillips on 22 Mar 2010 13:09
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" <eflip(a)discussions.microsoft.com> wrote in message
news:5B78F8FD-77DD-44D6-A8B1-10F87B37CE35(a)microsoft.com...
> 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.
>
>


From: eflip on 22 Mar 2010 15:34
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" <eflip(a)discussions.microsoft.com> wrote in message
> news:5B78F8FD-77DD-44D6-A8B1-10F87B37CE35(a)microsoft.com...
> > 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.
> >
> >
>
>
> .
>
From: Paul C on 22 Mar 2010 15:52
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" <eflip(a)discussions.microsoft.com> wrote in message
> > news:5B78F8FD-77DD-44D6-A8B1-10F87B37CE35(a)microsoft.com...
> > > 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.
> > >
> > >
> >
> >
> > .
> >
 |  Next  |  Last
Pages: 1 2
Prev: HLOOKUP into another xls file
Next: Question about syntax for conditional formatting (RESOLVED)