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

msarrasin5

New Member
Joined
Apr 18, 2019
Messages
2
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
Joined
Nov 1, 2008
Messages
3,734
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
1002Lagerfeld

<tbody>
</tbody>

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

Pat IDNameDOBDoctor
1000Andrew14-5-1967Coates
1001Sarah23-7-2012Dobson
1002Geoff4-4-1995Lagerfeld
1003Tim5-7-1986Dobson

<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
Joined
Apr 18, 2019
Messages
2
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 IDDoctor
1003Dobson
1002Lagerfeld

<tbody>
</tbody>

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

Pat IDNameDOBDoctor
1000Andrew14-5-1967Coates
1001Sarah23-7-2012Dobson
1002Geoff4-4-1995Lagerfeld
1003Tim5-7-1986Dobson

<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
 

Watch MrExcel Video

Forum statistics

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

Which adblocker are you using?

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
Top