Excel Match Formula

[Robert_Conklin]

I need an excel formula that will match the name from column E from the first spreadsheet to the name in column C from the second spreadsheet (both in the same workbook) and return the value in column D from the second spreadsheet.

 

[Peter_SSs]

Have a look at the VLOOKUP function

 

[Robert_Conklin]

The vlookup function will not work because my arrays are on two different spreadsheets, and the data I need returned is in a different column. Here is what I need in a nutshell. I have a list of users and a giant list of users, company wide that contains a Global ID and an Active Directory ID. I need to match the names from my list to the giant list of users and return the Global ID to my list. I can then modify the formula to return the Active Directory ID to another column

 

[Peter_SSs]

The vlookup function will not work because my arrays are on two different spreadsheets

Not sure what problem you envisage using two different sheets?

Robert_Conklin 2020-03-04 1.xlsm
	C	D	E
1	User	Global ID	Active Directory ID
2	Name 1	ID 1	456x
3	Name 5	ID 5	ygf
4	Name 3	ID 3	sd4
5	Name 2	ID 2	fgh
Giant List

Robert_Conklin 2020-03-04 1.xlsm
	E	F	G
1	Name	Global ID	Active Directory ID
2	Name 1	ID 1	456x
3	Name 7	Not found	Not found
4	Name 5	ID 5	ygf
List

Cell Formulas
Range	Formula
F2:F4	F2	=IFERROR(VLOOKUP(E2,'Giant List '!C$1:D$5,2,0),"Not found")
G2:G4	G2	=IFERROR(VLOOKUP(E2,'Giant List '!C$1:E$5,3,0),"Not found")

 

[Robert_Conklin]

My sheet has specific users. The giant sheet contains all users across our company. I only need the Global IDs and the Active Directory IDs for the names on my list. Rather than search each name individually, it would be much faster using a formula. The giant sheet was provided to me from another source.

 

[Peter_SSs]

it would be much faster using a formula.

I gave you a formula. In what way does it not satisfy your needs?
My sample 'Giant List' sheet contains users across all the company. My 'List' sheet contains the names on my list. The formulas have retrieved the 2 sets of data for those on my list as far as I can tell.

Perhaps you could make up a small set of dummy data for each sheet and show those, using XL2BB so we can copy to test with. Also include the expected results so we actually know what you want since trying to interpret what you have said so far apparently hasn't been clear enough to me.

BTW, I also suggest that you update your Account details (click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version.

 

[Robert_Conklin]

I apologize. I thought you were asking me a question. I will work with those formulas today and let you know.

Thank you.

 

[Excel777]

thanks

 

[Robert_Conklin]

I tried a modified formula to encompass the actual arrays:

=IFERROR(VLOOKUP(E:E,'Company Wide Global ID & AD ID'!A:A,2,0),"Not found")

Even though I looked up a couple of the names to see if they were on the giant list, which they were, it still brought back 'Not Found'.

 

[Robert_Conklin]

I played around with the Index/Match combo and developed the following that worked:

=INDEX(Table15[Global ID],MATCH([@Name],Table15[Name],0))

Thank you for all of your help!