vlookup, I suppose

[diggory]

Excel 2007. I have two spreadsheets that I am trying to cross reference. Both spreadsheets contain patient names, but only one spreadsheet contains ID numbers associated with each patient. I am trying to take those ID numbers from the first spreadsheet and search for their respective names in the second sheet, and then match the ID numbers to each name on the second spreadsheet.

Here is what I have tried so far, but I am not getting any positive results:

=IF(ISERROR(VLOOKUP($A4, Master!$A$1:$G$1000,1, FALSE)), "Master!", VLOOKUP($A4, Master!$A$1:$G$1000,1, FALSE))

but this only yields "Master!" for each cell.

Anyone have any ideas?

 

[jmthompson]

Your formula is looking for a match in column A of Master and wants to return a value from the same cell.

Make sure that the patient names are in column A of Master! and change the 1 to the column with the ID#'s. If in G, the 1 should be 7.

 

[diggory]

Thanks Jen.

I have the patient names in column A of master and IDs in B, and I am trying to insert them to the respective names in sheet2 column A where the names are in column B.

 

[jmthompson]

Then you can't use vlookup, you'll need Index/Match. I'm not as well versed in that style of formula, but I will dive into some old workbooks for you. If nobody else posts the solution first.

 

[jmthompson]

Try this:

=INDEX(Sheet2!A:G,MATCH(A4,Sheet2!G:G,0),1)

 

[diggory]

That is only showing "0" for every cell. Really frustrated with this project!

 

[jmthompson]

Sorry, did you replace Sheet2 with Master?

 

[jmthompson]

Sorry, try this:

=INDEX(Master!A:B,MATCH(A4,Master!B:B,0),1)

 

[diggory]

You are THE BEST!!!!

 

[jmthompson]

note, if there are records for two or more people with the same name, the first ID will always be returned