Multiple Cell Table Lookup

[Redlad91]

Hi,

I am looking for some help in finding an excel formula which will look up to manual values against a table on another sheet and give me the number in return.

For example, On sheet 1 I am looking search for the following values, Colleague name and period.

Within the table on Sheet 2 i have the colleagues names vertical on the left hand side of the sheet and the periods horizontally across the top of the sheet.

From here i want to manually type the name of the person i am looking for along with the period, for this to then find the value for both of these.

Example;

Sheet 1

Colleague Name: Bob
Period: P2

Result: 100

Sheet 2

Colleague name Period 1 Period 2 Period 3 Period 4

Bob 12 100 45 23
Anne 11 12 34 76
Dave 6 23 19 44

 

[Special-K99]

No cell references mentioned at all so I'll have to guess and come up with a working solution and you then have to adjust the formula according to your spreadsheet
I'm ignoring blank lines in your examples

Heres where I'm assuming the data is
Colleague Name in Sheet1 is in A1
Bob in Sheet1 is in B1

Colleague Name in Sheet2 is in A1
Bob in Sheet2 is in A2

FYI

You cant attach files on this forum. There are tools on this forum for adding small spreadsheet images

https://www.mrexcel.com/forum/about-board/508133-attachments.html

Or upload the file to an online storage site and post a link to it, though some people may not download the file for fear of viruses.


Otherwise try this

in Sheet1!B3
=INDEX(Sheet2!B$2:E$4,MATCH(B1,Sheet2!A$2:A$4,0),MATCH(B2,Sheet2!B$1:E$1,0))