How to use If Statement or Vlookup when comparing lists of names

[Dprobasco]

I have a spreadsheet with three worksheets

The first worksheet contains a list of 5045 names of people in one column and related individual ID numbers in another column

The second spreadsheet has a list of 345 names of people in a column

The third spreadsheet has a list of 200 names of people in a column

For each of the second and third spreadsheets I want to compare that list to the longer list and when it matches, put the related ID number in the next column.

Which is better to use, the If statement or the Vlookup statement.

How would I use the statement to make it work.

I tried the if statement but ended up with the Spill error message

Dale

 

[etaf]

index/match or xlookup

not sure what ranges you are using

assumin the ID is in sheet1 column B

=index(sheet1!B2:B10000,MATCH(A2,sheet1!A2:A10000,0))

A SMALL sample spreadsheet, around 10-20 rows, would help a lot here, with all sensitive data removed, and expected results mocked up and manually entered, with a few notes of explanation.

This will possibly enable a quicker and more accurate solution for you.

MrExcel has a tool called “XL2BB” that lets you post samples of your data and will allow us to copy/paste your sample data into our Excel spreadsheets, saving a lot of time.

XL2BB - Excel Range to BBCode

Excel 'mini-sheet' in messages - XL2BB Although experts prefer to read your description and question instead of working in your actual file to solve your problem, there are times that it is difficult to explain an issue without providing actual...

www.mrexcel.com

You can also test to see if it works ok, in the "Test Here" forum.

Test Here

Use this forum to test your signature, learn bbcode, smilies, XL2BB, etc. Threads in this forum are automatically deleted after no replies for seven (7) days

www.mrexcel.com

OR if you cannot get XL2BB to work, or have restrictions on your PC

then put the sample spreadsheet onto a share

I only tend to goto OneDrive, Dropbox or google docs , as I'm never certain of other random share sites and possible virus.
Please make sure you have a representative data sample and also that the data has been desensitised, remember this site is open to anyone with internet access to see - so any sensitive / personal data should be removed

Make sure you set any share or google to share to everyone

 

[Dprobasco]

index/match or xlookup

not sure what ranges you are using

assumin the ID is in sheet1 column B

=index(sheet1!B2:B10000,MATCH(A2,sheet1!A2:A10000,0))

A SMALL sample spreadsheet, around 10-20 rows, would help a lot here, with all sensitive data removed, and expected results mocked up and manually entered, with a few notes of explanation.

This will possibly enable a quicker and more accurate solution for you.

MrExcel has a tool called “XL2BB” that lets you post samples of your data and will allow us to copy/paste your sample data into our Excel spreadsheets, saving a lot of time.

XL2BB - Excel Range to BBCode

Excel 'mini-sheet' in messages - XL2BB Although experts prefer to read your description and question instead of working in your actual file to solve your problem, there are times that it is difficult to explain an issue without providing actual...

www.mrexcel.com

You can also test to see if it works ok, in the "Test Here" forum.

Test Here

Use this forum to test your signature, learn bbcode, smilies, XL2BB, etc. Threads in this forum are automatically deleted after no replies for seven (7) days

www.mrexcel.com

OR if you cannot get XL2BB to work, or have restrictions on your PC

then put the sample spreadsheet onto a share

I only tend to goto OneDrive, Dropbox or google docs , as I'm never certain of other random share sites and possible virus.
Please make sure you have a representative data sample and also that the data has been desensitised, remember this site is open to anyone with internet access to see - so any sensitive / personal data should be removed

Make sure you set any share or google to share to everyone

Thanks so much for your quick reply.

I will try the upload later today but heading out now to donate blood.

 

[Dprobasco]

index/match or xlookup

not sure what ranges you are using

assumin the ID is in sheet1 column B

=index(sheet1!B2:B10000,MATCH(A2,sheet1!A2:A10000,0))

A SMALL sample spreadsheet, around 10-20 rows, would help a lot here, with all sensitive data removed, and expected results mocked up and manually entered, with a few notes of explanation.

This will possibly enable a quicker and more accurate solution for you.

MrExcel has a tool called “XL2BB” that lets you post samples of your data and will allow us to copy/paste your sample data into our Excel spreadsheets, saving a lot of time.

XL2BB - Excel Range to BBCode

Excel 'mini-sheet' in messages - XL2BB Although experts prefer to read your description and question instead of working in your actual file to solve your problem, there are times that it is difficult to explain an issue without providing actual...

www.mrexcel.com

You can also test to see if it works ok, in the "Test Here" forum.

Test Here

Use this forum to test your signature, learn bbcode, smilies, XL2BB, etc. Threads in this forum are automatically deleted after no replies for seven (7) days

www.mrexcel.com

OR if you cannot get XL2BB to work, or have restrictions on your PC

then put the sample spreadsheet onto a share

I only tend to goto OneDrive, Dropbox or google docs , as I'm never certain of other random share sites and possible virus.
Please make sure you have a representative data sample and also that the data has been desensitised, remember this site is open to anyone with internet access to see - so any sensitive / personal data should be removed

Make sure you set any share or google to share to everyone

I have been trying to get the addin loaded but can’t seem to get it to load.

Further explanation on my question

In a sheet called Part I have the following
I have a list of 5045 items with an ID number in column A and in Column B I have the persons name.

In a second sheet called Client I have a list of 377 people with their name in column A

I have a third sheet called Volunteer with a list of 145 people with their name in column A.

I want to have Excel compare the list in sheet Part to the list in the sheet called Client. When it finds a match then tale the ID number from the appropriate cell next to the comparable name and copy it next to the comparable name Excel found in the sheet called Client.

The list in the Part sheet includes people that have been either a Client or Volunteer over the years. Some of the people in this long list may have moved, died, etc. so are no longer active.

The list in the Client worksheet are the Clients that are actively receiving services from us and thus want to get their ID linked to their name.

A similar issue is with the sheet called Volunteer.

Unfortunately, the DB program only provides the longer list with the ID number so I am trying to link the proper name with the proper ID number.

Dale

 

[etaf]

In a sheet called Part I have the following

I have a list of 5045 items with an ID number in column A and in Column B I have the persons name.

OK, so same sort of formula as i posted
Index( the ID range wanted to return from part , match ( cell with name , the name range in part , 0)

in each sheet - client put

=index( part!$A$2:$A$10000, match( A2 , part!$B$2:$A$10000, 0))

same for sheet volunteer

this will use the match of name in A2 , and look down the column B in part sheet until it finds an exact match of the name and then return the value from column A the ID from part sheet

 

[Dprobasco]

OK, so same sort of formula as i posted
Index( the ID range wanted to return from part , match ( cell with name , the name range in part , 0)

in each sheet - client put

=index( part!$A$2:$A$10000, match( A2 , part!$B$2:$A$10000, 0))

same for sheet volunteer

this will use the match of name in A2 , and look down the column B in part sheet until it finds an exact match of the name and then return the value from column A the ID from part sheet

Ok once again thanks for the quick response.
It works great. I did note the typo in the last part of the formula part!$B$2:$A$10000,0)). Changed the A to a B.

Again great response, so thankful.

 

[Peter_SSs]

Since you have MS365 you can get all the results with a single formula instead of copying down the hundreds of rows you have. You can still use INDEX/MATCH or XLOOKUP as already suggested with the whole range at once but here is an option with VLOOKUP. You just need to adjust the ranges to suit your data.

Dprobasco.xlsm
	A	B
1	ID	Name
2	ID1	Name 1
3	ID2	Name 2
4	ID3	Name 3
5	ID4	Name 4
6	ID5	Name 5
7	ID6	Name 6
8	ID7	Name 7
9	ID8	Name 8
10	ID9	Name 9
Part

Dprobasco.xlsm
	A	B
1	Client	ID
2	Name 3	ID3
3	Name 8	ID8
4	Name12	Not found
5	Name 1	ID1
6
Client

Cell Formulas
Range	Formula
B2:B5	B2	=IFNA(VLOOKUP(A2:A5,CHOOSECOLS(Part!A2:B10000,2,1),2,0),"Not found")
Dynamic array formulas.

Dprobasco.xlsm
	A	B
1	Volunteer	ID
2	Name 5	ID5
3	Name 55	Not found
4	Name 2	ID2
5	Name 7	ID7
6	Name 6	ID6
7
Volunteer

Cell Formulas
Range	Formula
B2:B6	B2	=IFNA(VLOOKUP(A2:A6,CHOOSECOLS(Part!A2:B10000,2,1),2,0),"Not found")
Dynamic array formulas.

 

[Dprobasco]

Ok another question, I now have worksheets of Part, Client and Volunteer.

Using the list of names in the Client sheet, check for matches in the Part sheet and when found insert the word Client next to the match.

I would also do the same for the Volunteer sheet.

I am guessing i use something similar for this but can’t seem to get it to work.

Dale

 

[Dprobasco]

Ok another question, I now have worksheets of Part, Client and Volunteer.

Using the list of names in the Client sheet, check for matches in the Part sheet and when found insert the word Client next to the match.

I would also do the same for the Volunteer sheet.

I am guessing i use something similar for this but can’t seem to get it to work.

Dale

Just an update on my last request. Using all the info provided I got it figured out so thanks to you all

Dale

 

[Peter_SSs]

Using all the info provided I got it figured out so thanks to you all

Good news, thanks for letting us know.
Not sure how you did it but this is how I might have done it using the same sample Client and Volunteer sheets I used above. Again, all the results at once with a 'spill' formula.

Dprobasco.xlsm
	A	B	C
1	ID	Name
2	ID1	Name 1	Client
3	ID2	Name 2	Volunteer
4	ID3	Name 3	Client
5	ID4	Name 4	Not found
6	ID5	Name 5	Volunteer
7	ID6	Name 6	Volunteer
8	ID7	Name 7	Volunteer
9	ID8	Name 8	Client
10	ID9	Name 9	Not found
Part

Cell Formulas
Range	Formula
C2:C10	C2	=IF(ISNUMBER(XMATCH(B2:B10,Client!A2:A10000)),"Client",IF(ISNUMBER(XMATCH(B2:B10,Volunteer!A2:A10000)),"Volunteer","Not found"))
Dynamic array formulas.