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?

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?

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?

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?

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

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