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