for a column of values, search file that contains the value and another piece of needed data - Vlookup?

[msarrasin5]

I have a file A that contains a column of 50 patient IDs for which I need the matching doctor. I have a file B that contains a column of patient IDs, including those I'm looking for, and a column of associated doctors (as well as other columns). Each column in file B has 3000 values.

For each ID in file A, I want to find the same ID in the ID column in file B, and return the doctor name in the same row to the field next to the ID column in File A.

I'm using the 2 file example for clarity but I understand the entire function can/would be done in one file

What is the correct function and how would I do it?

 

[sijpie]

You need the VLOOKUP function in the cells in File A next to the patient ID. You will make it much easier if it is in one workbook. But if you work from two workbooks then make sure both are open and when you type in the formula, use the mouse to select the ranges you want. Excel will then put in the correct file/sheet and range names.

File A (or sheet A if in one file)
[TABLE="class: grid, width: 128"]
<tbody>[TR]
[TD="width: 64"]Pat ID
[/TD]
[TD="width: 64"]Doctor[/TD]
[/TR]
[TR]
[TD="align: right"]1003
[/TD]
[TD]Dobson
[/TD]
[/TR]
[TR]
[TD="align: right"]1002[/TD]
[TD]Lagerfeld
[/TD]
[/TR]
</tbody>[/TABLE]
[TABLE="width: 128"]
<tbody>[TR]
[/TR]
[TR]
[/TR]
[TR]
[/TR]
</tbody>[/TABLE]
File B (or sheet B if in one file)

[TABLE="class: grid, width: 258"]
<tbody>[TR]
[TD]Pat ID[/TD]
[TD]Name[/TD]
[TD]DOB[/TD]
[TD]Doctor[/TD]
[/TR]
[TR]
[TD="align: right"]1000[/TD]
[TD]Andrew[/TD]
[TD="align: right"]14-5-1967[/TD]
[TD]Coates[/TD]
[/TR]
[TR]
[TD="align: right"]1001[/TD]
[TD]Sarah[/TD]
[TD="align: right"]23-7-2012[/TD]
[TD]Dobson[/TD]
[/TR]
[TR]
[TD="align: right"]1002[/TD]
[TD]Geoff[/TD]
[TD="align: right"]4-4-1995[/TD]
[TD]Lagerfeld[/TD]
[/TR]
[TR]
[TD="align: right"]1003[/TD]
[TD]Tim[/TD]
[TD="align: right"]5-7-1986[/TD]
[TD]Dobson[/TD]
[/TR]
</tbody>[/TABLE]

The formula in the second column (cell B2) in Sheet A is
=VLOOKUP(A2,'Sheet B'!$A$2:$D$5,4,FALSE)

Then copy the formula down.

How it works:
=VLOOKUP(A2,
Take the value in cell A2

=VLOOKUP(A2,'Sheet B'!$A$2:$D$5,
Find it on Sheet B in the range A2:D5. Of course in your table the ID will be in one column, so why do you need to specify up to column D? That is because the value you want returned, the Doctor name, is in column D.
The $$ signs you have to put in because you want to copy the formula down, and else the address would change.

=VLOOKUP(A2,'Sheet B'!$A$2:$D$5,4,
4: Return me the name in the 4th colummn in the range A2:D5, the column where the doctor's name is held

=VLOOKUP(A2,'Sheet B'!$A$2:$D$5,4,FALSE)
If the column of IDs on sheet B is ordered (small to large) then you can leave out False, or put in TRUE. I put FALSE in in case the numbers are not in order. There is another use ofr it as well, lookup the function description

Of course you need to moduify the addresses in the function to suit your tables and sheet names

 

[msarrasin5]

Thank you, this is great.

You need the VLOOKUP function in the cells in File A next to the patient ID. You will make it much easier if it is in one workbook. But if you work from two workbooks then make sure both are open and when you type in the formula, use the mouse to select the ranges you want. Excel will then put in the correct file/sheet and range names.

File A (or sheet A if in one file)
[TABLE="class: grid, width: 128"]
<tbody>[TR]
[TD="width: 64"]Pat ID[/TD]
[TD="width: 64"]Doctor[/TD]
[/TR]
[TR]
[TD="align: right"]1003[/TD]
[TD]Dobson[/TD]
[/TR]
[TR]
[TD="align: right"]1002[/TD]
[TD]Lagerfeld[/TD]
[/TR]
</tbody>[/TABLE]
[TABLE="width: 128"]
<tbody>[TR]
[/TR]
[TR]
[/TR]
[TR]
[/TR]
</tbody>[/TABLE]
File B (or sheet B if in one file)

[TABLE="class: grid, width: 258"]
<tbody>[TR]
[TD]Pat ID[/TD]
[TD]Name[/TD]
[TD]DOB[/TD]
[TD]Doctor[/TD]
[/TR]
[TR]
[TD="align: right"]1000[/TD]
[TD]Andrew[/TD]
[TD="align: right"]14-5-1967[/TD]
[TD]Coates[/TD]
[/TR]
[TR]
[TD="align: right"]1001[/TD]
[TD]Sarah[/TD]
[TD="align: right"]23-7-2012[/TD]
[TD]Dobson[/TD]
[/TR]
[TR]
[TD="align: right"]1002[/TD]
[TD]Geoff[/TD]
[TD="align: right"]4-4-1995[/TD]
[TD]Lagerfeld[/TD]
[/TR]
[TR]
[TD="align: right"]1003[/TD]
[TD]Tim[/TD]
[TD="align: right"]5-7-1986[/TD]
[TD]Dobson[/TD]
[/TR]
</tbody>[/TABLE]

The formula in the second column (cell B2) in Sheet A is
=VLOOKUP(A2,'Sheet B'!$A$2:$D$5,4,FALSE)

Then copy the formula down.

How it works:
=VLOOKUP(A2,
Take the value in cell A2

=VLOOKUP(A2,'Sheet B'!$A$2:$D$5,
Find it on Sheet B in the range A2:D5. Of course in your table the ID will be in one column, so why do you need to specify up to column D? That is because the value you want returned, the Doctor name, is in column D.
The $$ signs you have to put in because you want to copy the formula down, and else the address would change.

=VLOOKUP(A2,'Sheet B'!$A$2:$D$5,4,
4: Return me the name in the 4th colummn in the range A2:D5, the column where the doctor's name is held

=VLOOKUP(A2,'Sheet B'!$A$2:$D$5,4,FALSE)
If the column of IDs on sheet B is ordered (small to large) then you can leave out False, or put in TRUE. I put FALSE in in case the numbers are not in order. There is another use ofr it as well, lookup the function description

Of course you need to moduify the addresses in the function to suit your tables and sheet names