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

msarrasin5

New Member
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?

Excel Facts

Can a formula spear through sheets?
Use =SUM(January:December!E7) to sum E7 on all of the sheets from January through December

sijpie

Well-known Member
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)
 Pat ID Doctor 1003 Dobson 1002 Lagerfeld

<tbody>
</tbody>

<tbody>
</tbody>
File B (or sheet B if in one file)

 Pat ID Name DOB Doctor 1000 Andrew 14-5-1967 Coates 1001 Sarah 23-7-2012 Dobson 1002 Geoff 4-4-1995 Lagerfeld 1003 Tim 5-7-1986 Dobson

<tbody>
</tbody>

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

Last edited:

msarrasin5

New Member
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)
 Pat ID Doctor 1003 Dobson 1002 Lagerfeld

<tbody>
</tbody>

<tbody>
</tbody>
File B (or sheet B if in one file)

 Pat ID Name DOB Doctor 1000 Andrew 14-5-1967 Coates 1001 Sarah 23-7-2012 Dobson 1002 Geoff 4-4-1995 Lagerfeld 1003 Tim 5-7-1986 Dobson

<tbody>
</tbody>

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

Replies
0
Views
50
Replies
2
Views
73
Replies
3
Views
104
Replies
9
Views
322
Replies
1
Views
89

Threads
1,129,430
Messages
5,636,227
Members
416,908
Latest member
Streetsweeper

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back