Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Index/Match formula | Excel Discussion (Misc queries) | |||
help with Index and Match Formula | Excel Worksheet Functions | |||
index, match formula | Excel Worksheet Functions | |||
INDEX MATCH formula | Excel Worksheet Functions | |||
How do I display more than one match in a Index/Match formula? | Excel Worksheet Functions |