| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Match Multiple Values
I have 2 variable values in spreadsheet 1 that match 2 values in spreadsheet
2. The B/MarkCost needs to be reported back to Spreadsheet 1. Any ideas on how to do it? Eg. Spreadsheet 1 To Suburb Serv B/markCost ALBION E ALDERL N ALBION N Spreadsheet 2 TO SERV B/MarkCOST ALBION E $37.06 ALDERL N $14.96 ALEXAN E $31.35 A.OVER G $62.70 ALBION N $7.80 ALBION A $78.40 |
|
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Match Multiple Values
In Sheet1,
Put this in C2, normal ENTER: =INDEX(Sheet2!C$2:C$7,MATCH(1,INDEX((Sheet2!A$2:A$ 7=A2)*(Sheet2!B$2:B$7=B2),),0)) Copy C2 down. Adapt the ranges to suit. This expression is generic, it'll work even if Sheet2!C$2:C$7 were to contain text/mixed data to be returned, which sumproduct cannot handle. Celebrate your success, click the YES button below -- Max Singapore http://savefile.com/projects/236895 Downloads:25,000 Files:300 Subscribers:70 xdemechanik --- "Cazulu" wrote: I have 2 variable values in spreadsheet 1 that match 2 values in spreadsheet 2. The B/MarkCost needs to be reported back to Spreadsheet 1. Any ideas on how to do it? Eg. Spreadsheet 1 To Suburb Serv B/markCost ALBION E ALDERL N ALBION N Spreadsheet 2 TO SERV B/MarkCOST ALBION E $37.06 ALDERL N $14.96 ALEXAN E $31.35 A.OVER G $62.70 ALBION N $7.80 ALBION A $78.40 |
|
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Match Multiple Values
Try this:
=SUMPRODUCT(--(Sheet2!A$2:A$7=A2),--(Sheet2!B$2:B$7=B2),Sheet2!C$2:C$7) -- Biff Microsoft Excel MVP "Cazulu" wrote in message ... I have 2 variable values in spreadsheet 1 that match 2 values in spreadsheet 2. The B/MarkCost needs to be reported back to Spreadsheet 1. Any ideas on how to do it? Eg. Spreadsheet 1 To Suburb Serv B/markCost ALBION E ALDERL N ALBION N Spreadsheet 2 TO SERV B/MarkCOST ALBION E $37.06 ALDERL N $14.96 ALEXAN E $31.35 A.OVER G $62.70 ALBION N $7.80 ALBION A $78.40 |
|
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Match Multiple Values
Thanks for your help Max and T.V
"Cazulu" wrote: I have 2 variable values in spreadsheet 1 that match 2 values in spreadsheet 2. The B/MarkCost needs to be reported back to Spreadsheet 1. Any ideas on how to do it? Eg. Spreadsheet 1 To Suburb Serv B/markCost ALBION E ALDERL N ALBION N Spreadsheet 2 TO SERV B/MarkCOST ALBION E $37.06 ALDERL N $14.96 ALEXAN E $31.35 A.OVER G $62.70 ALBION N $7.80 ALBION A $78.40 |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Match multiple values | Excel Worksheet Functions | |||
| Find a Match in Multiple Places & Return Multiple Values | Excel Worksheet Functions | |||
| Returning MULTIPLE values with Index and Match | Excel Discussion (Misc queries) | |||
| How do I add multiple values that match multiple conditions? | Excel Discussion (Misc queries) | |||
| match/index using multiple values | Excel Worksheet Functions | |||
Linear Mode
