Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Index lookup with multiple match criteria
I have two tables. one that is inventory related and another that is
forecast related. I need to tie the inventory data to the forecast data. The issue I have is that I have the sku number for 13 different regions. How do I develop the index/match function so that it looks up the data that matches the region and the sku? |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Index lookup with multiple match criteria
Try the below to return a match from col C.....region in col A and sku in
ColB. Adjust the range to suit your requirement =INDEX($C$2:$C$10,MATCH(1,($A$2:$A$10=region)*($B$ 2:$B$10=sku),0)) If this post helps click Yes --------------- Jacob Skaria "Vance K" wrote: I have two tables. one that is inventory related and another that is forecast related. I need to tie the inventory data to the forecast data. The issue I have is that I have the sku number for 13 different regions. How do I develop the index/match function so that it looks up the data that matches the region and the sku? |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Index lookup with multiple match criteria
Please note that this is an array formula. Within the cell in edit mode (F2)
paste this formula and press Ctrl+Shift+Enter to apply this formula. If successful in 'Formula Bar' you can notice the curly braces at both ends like "{=<formula}" If this post helps click Yes --------------- Jacob Skaria "Jacob Skaria" wrote: Try the below to return a match from col C.....region in col A and sku in ColB. Adjust the range to suit your requirement =INDEX($C$2:$C$10,MATCH(1,($A$2:$A$10=region)*($B$ 2:$B$10=sku),0)) If this post helps click Yes --------------- Jacob Skaria "Vance K" wrote: I have two tables. one that is inventory related and another that is forecast related. I need to tie the inventory data to the forecast data. The issue I have is that I have the sku number for 13 different regions. How do I develop the index/match function so that it looks up the data that matches the region and the sku? |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Index lookup with multiple match criteria
I tried the formula but got an #N/A error. Let me give you an example of the
tables Table 1 Forecast A B C Region SKU Forecast EU 1234 100 EU 5678 200 US 1234 100 US 5678 50 AU 1234 25 AU 5678 10 Table 2 Inventory A B C Region SKU Inv EU 1234 10 EU 5678 20 US 1234 100 US 5678 100 AU 1234 15 AU 5678 1 Desired result A B C D Region SKU Forecast Inv EU 1234 100 10 EU 5678 200 20 US 1234 100 100 US 5678 50 100 AU 1234 25 15 AU 5678 10 1 "Jacob Skaria" wrote: Please note that this is an array formula. Within the cell in edit mode (F2) paste this formula and press Ctrl+Shift+Enter to apply this formula. If successful in 'Formula Bar' you can notice the curly braces at both ends like "{=<formula}" If this post helps click Yes --------------- Jacob Skaria "Jacob Skaria" wrote: Try the below to return a match from col C.....region in col A and sku in ColB. Adjust the range to suit your requirement =INDEX($C$2:$C$10,MATCH(1,($A$2:$A$10=region)*($B$ 2:$B$10=sku),0)) If this post helps click Yes --------------- Jacob Skaria "Vance K" wrote: I have two tables. one that is inventory related and another that is forecast related. I need to tie the inventory data to the forecast data. The issue I have is that I have the sku number for 13 different regions. How do I develop the index/match function so that it looks up the data that matches the region and the sku? |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Index lookup with multiple match criteria
On Jul 16, 10:50*pm, Vance K wrote:
I tried the formula but got an #N/A error. *Let me give you an example of the tables Table 1 Forecast A * * * * * * * *B * * * * * * * *C Region * * * * SKU * * * * * *Forecast EU * * * * * * * *1234 * * * * *100 EU * * * * * * * *5678 * * * * *200 US * * * * * * * *1234 * * * * *100 US * * * * * * * *5678 * * * * * 50 AU * * * * * * * *1234 * * * * * 25 AU * * * * * * * *5678 * * * * * 10 Table 2 Inventory A * * * * * * * *B * * * * * * * *C Region * * * * SKU * * * * * *Inv EU * * * * * * * *1234 * * * * *10 EU * * * * * * * *5678 * * * * *20 US * * * * * * * *1234 * * * * *100 US * * * * * * * *5678 * * * * *100 AU * * * * * * * *1234 * * * * * 15 AU * * * * * * * *5678 * * * * * 1 Desired result A * * * * * * * *B * * * * * * * *C * * * * * * * * *D Region * * * * SKU * * * * * *Forecast * * * Inv EU * * * * * * * *1234 * * * * *100 * * * * * * *10 EU * * * * * * * *5678 * * * * *200 * * * * * * * 20 US * * * * * * * *1234 * * * * *100 * * * * * * *100 US * * * * * * * *5678 * * * * * 50 * * * * * * * 100 AU * * * * * * * *1234 * * * * * 25 * * * * * * * 15 AU * * * * * * * *5678 * * * * * 10 * * * * * * * 1 "Jacob Skaria" wrote: Please note that this is an array formula. Within the cell in edit mode (F2) paste this formula and press Ctrl+Shift+Enter to apply this formula. If successful in 'Formula Bar' you can notice the curly braces at both ends like "{=<formula}" If this post helps click Yes --------------- Jacob Skaria "Jacob Skaria" wrote: Try the below to return a match from col C.....region in col A and sku in ColB. Adjust the range to suit your requirement =INDEX($C$2:$C$10,MATCH(1,($A$2:$A$10=region)*($B$ 2:$B$10=sku),0)) If this post helps click Yes --------------- Jacob Skaria "Vance K" wrote: I have two tables. *one that is inventory related and another that is forecast related. *I need to tie the inventory data to the forecast data. * The issue I have is that I have the sku number for 13 different regions. *How do I develop the index/match function so that it looks up the data that matches the region and the sku? Hello Vance, Here a solution for Table 1. You should be able to replicate this for the other tables too Solution 1 uses string concatenation & Solution 2 uses an IF function. Both solutions use the INDEX & MATCH functions. Further explanation is available on this MS Knowledge base link http://support.microsoft.com/kb/214142 Region SKU * * * * * *Forecast EU * * * *1234 * * * * *100 EU * * * *5678 * * * * *200 US * * * *1234 * * * * *100 US * * * *5678 * * * * * 50 AU * * * *1234 * * * * * 25 AU * * * *5678 * * * * * 10 EU * * * *1234 * * * * *100 <===== =INDEX(C2:C7,MATCH (A12&B12,A2:A7&B2:B7,1),0) EU * * * *1234 * * * * *100 <===== =INDEX(C2:C7,MATCH(A13,IF (B2:B7=B13,A2:A7)),0) Hope this answers your question. Cheers Abdul Hafeel |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Index lookup with multiple match criteria
PLEASE NOTE BOTH THE ABOVE ARE ARRAY FORMULAE. You should enter then by pressing Ctrl + Shift + Enter. Cheers Abdul Hafeel |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Four criteria MATCH INDEX lookup of date between matching two text | Excel Worksheet Functions | |||
challenge! - match/index/lookup with multiple criteria | Excel Worksheet Functions | |||
Index & Match functions - multiple criteria and multiple results | Excel Worksheet Functions | |||
Index/Match - Lookup based on multiple column criteria | Excel Worksheet Functions | |||
Index/Match Multiple Criteria | Excel Discussion (Misc queries) |