Lookup data in another tab based on multiple criteria

[Paul36]

Hi I need help to look up data in another tab based on multiple criteria.

As an example, lets say in Sheet 1, I have the following data:
A1 = month (eg 5 for May)
A2 = Code Number 1
A3 = Code Number 2

In Sheet 2, I have the data:
A2 = Code Number 1
A3 = Code Number 2

In Sheet 2, from B1 to B13 is the months of the year (1-12)
And also B2 to B13 are the values for Code Number 1 for each of these months (and C2 to C13 are values for Code Number 2 etc).

So back in Sheet 1, I want to have a formula that returns in Cell B2 the value for the Code Number 1 that is in the relevant month in sheet 2. In Sheet 1, I need to reference the Code Number and current month, and then based on these 2 criteria, search for the correct value in Sheet 2 that has the same month and Code Number.

So if I input "4" in Cell A1 in Sheet 1, this would mean it is April. The formula in Sheet 1, Cell B2 would then look in Sheet 2 and return the value for that Code Number for April.

Hope this makes sense. It is doing my head in! ANy help much appreciated

 

[Aladin Akyurek]

On Sheet2, you have months in B1:B13, but also codes in B2:B13. This sounds wrong...

 

[Paul36]

Sorry, I messed up in my explanation of Sheet 2.

It should read:

In Sheet 2, from B1 to M1 is the months of the year (1-12)
And also B2 to M2 are the values for Code Number 1 for each of these months (and B3 to M3 are values for Code Number 2 etc)

I would post a copy of my tabs but not sure how to do that, sorry!

 

[Aladin Akyurek]

Sorry, I messed up in my explanation of Sheet 2.

It should read:

In Sheet 2, from B1 to M1 is the months of the year (1-12)
And also B2 to M2 are the values for Code Number 1 for each of these months (and B3 to M3 are values for Code Number 2 etc)

I would post a copy of my tabs but not sure how to do that, sorry!

B2, Sheet1

=INDEX(Sheet2!$B$2:$M$100,
    MATCH(A2,Sheet2!$A$2:$A$100,0),
    MATCH($A$1,Sheet2!$B$1:$M$1,0))

which can be copied down for the rest of the look up
values in A3 and downwards.

 

[Paul36]

Thanks mate, that is exactly what I wanted! Great stuff

 

[Aladin Akyurek]

Thanks mate, that is exactly what I wanted! Great stuff

You are welcome. Thanks for providing feedback.