Correlating data in 2 sheets. Vlookup, Match Index ?

Dear All,

This is my first post on this forum. I have racked my brains trying to come up with an adequate solution but I haven't been able to find one that works so far. My problem is as follows:

I have a database which includes a list of people's names, surnames and I.D. numbers (in that order, in colums K, L and M respectively). These are on a sheet named "EMTP". These entries I just mentioned can be repeated in more than one row. On another sheet named "Patient List" I have the reference list of individual I.D. numbers, people's names and surnames, in columns A, B and C respectively, which I want to use as a reference.

I somehow want to link the information in the reference list to the main information on the "EMTP" sheet in a way that will allow me to input the I.D. number only (in column M), and have the associated information (name and surname) pop up automatically in the adjacent cells in columns K and L, IF the patient is already in my "reference list". If nothing pops up, I will know it is a new one so I will need to be able to insert it into my reference list for future use.

Below are what my sheets look like. I want to be able to input just the I.D. numbers into column M, so inputting 123M in column M would give me John in column K same row, and Doe in column N same row.

"EMTP" sheet:

K	L	M
Name	Surname	I.D.

"Patient List" sheet:

A	B	C
I.D.	Name	Surname
123M	John	Doe
555G	Mary	Wilkinson

Would anyone be able to help me in writing this formula because I'm at my wit's end.

Kindest Regards,
Sindagon

Assuming your headers are in row 1 and your data starts in row 2...

In EMTP: K2...

=INDEX('Patient List'!$B$2:$C$100,MATCH(EMTP!$M2,'Patient List'!$A$2:$A$100,0),MATCH(EMTP!$K1,'Patient List'!$B$1:$C$1,0))

Then just drag that formula across to L2 and down to the bottom of your data table. Then when you enter the ID number in column M of the 'EMTP' sheet, the names will populate automatically.

Hey jwillis07, thanks for your reply. Gonna try it now. I'll keep you posted.

Ok, so, the data starts in row 5774. I'm unsure as to where I have to insert the row value. The reference table in the "Patient List" sheet goes from row 1 to row 10,000.

So the patient list has headers in row 1 and data in rows 2-10,000?
And the EMTP sheet has headers in row 5773 and data in rows 5774 onwards?

=INDEX('Patient List'!$B$2:$C$10000,MATCH(EMTP!$M5774,'Patient List'!$A$2:$A$10000,0),MATCH(EMTP!$K5773,'Patient List'!$B$1:$C$1,0))