Derive a value across 2 worksheets

**JonUK01** · 03-26-2011, 03:19 AM

Hello, I'd be grateful for some help.

I've got 2 worksheets in the same workbook. Each sheet has a column containing an Employee ID Number. The number of records on each sheet is not the same and they are not sorted on the same criteria.

Sheet1

Col D - Employee ID
Col AI - Rating (currently blank cells)

Sheet 2

Col A - Employee ID
Col K - Rating

I would like a formula/function to go in Sheet 1 Col B cells which derives the Rating against an Employee ID based on the information held in Sheet 2.

I've tried to work out the formula, but get very confused with Lookup functions, which may not be applicable anyway.

Thanks in advance

**pike** · 03-26-2011, 03:39 AM

Hi JonUK01,
Welcome to the forum
you could use the combination of Index and Match

Please Login or Register  to view this content.

fill down

**TMS** · 03-26-2011, 03:41 AM

=VLOOKUP(D2,Sheet2!A:K,11,False)

Regards

**JonUK01** · 03-26-2011, 04:06 AM

Thanks for the answers.

Pike - I've entered the formula as shown and it comes up "#N/A".

I'm not sure whether I've exlained the problem correctly or entered the fomula correctly, so I've attached a sample file which will demonstrate what I would like to achieve.

**pike** · 03-26-2011, 04:38 AM

JonUK01
on the worksheet tabs make "Sheet 1" should be "Sheet1"

**pike** · 03-26-2011, 04:49 AM

Hi JonUK01,

arr looks like you cant change the sheet name

Please Login or Register  to view this content.

**JonUK01** · 03-26-2011, 07:31 PM

Thanks Pike,

Finally did it using your first formula. Realised that the Sheet naming problem was caused by a hidden sheet. Once that was renamed, I could match up with the formula. Also, the "#N/A" result appears to have been caused by some cells being formatted as text. Once that was sorted, it all worked!!

My thanks also to the ther offered option. I really appreciate the Forum's help.

I'll mark it as solved.