Index lookup with multiple match criteria

  • Thread starter Thread starter Vance K
  • Start date Start date
V

Vance K

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?
 
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
 
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
 
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
 
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
Click to expand...

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
 
PLEASE NOTE BOTH THE ABOVE ARE ARRAY FORMULAE. You should enter then
by pressing Ctrl + Shift + Enter.

Cheers

Abdul Hafeel
 
Back
Top