Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 2
Default 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.


  #2   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 269
Default Is this an Index/Match formula?

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.


  #3   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 834
Default Is this an Index/Match formula?

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.




  #4   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 2
Default Is this an Index/Match formula?

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.




.

  #5   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 269
Default Is this an Index/Match formula?

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.




.



  #6   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 834
Default 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.




.



Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Index/Match formula Chris Excel Discussion (Misc queries) 5 September 18th 09 10:52 PM
help with Index and Match Formula klafert Excel Worksheet Functions 1 May 9th 07 11:28 AM
index, match formula Todd Excel Worksheet Functions 1 June 27th 06 08:43 PM
INDEX MATCH formula Susan Excel Worksheet Functions 3 May 20th 06 10:57 AM
How do I display more than one match in a Index/Match formula? Trish Excel Worksheet Functions 0 September 26th 05 10:21 PM


All times are GMT +1. The time now is 10:26 AM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"