How to search for matching data and return result from a cell?

[Arinoum]

Hello, I'm not sure which formula to use to return the value from a Cell. I have 2 documents with all the same information except that one sheet does not have column for Login ID. I would like to add column Login ID but match it to match the same information on the rows. It needs to match the User number. I basically want it to search if A1 from first sheet = B1:B3000 from second sheet then return the value matching that from c1:C3000. I thought of a simple IF(A1=B1,C1,0) but this isn't working because the numbers change in column C and I need it to look in all of column C and A instead of just one cell match. Thank you!

User number	User name	Login ID

 

[SanjayGMusafir]

this isn't working because the numbers change in column C and I need it to look in all of column C and A instead of just one cell match

Need more clarity on how numbers change in Column C?

Further to give you an optimal solution would need few things as below -

# Update your current Excel Version and platform

# Share an XL2BB of actual data to understand the complexity of the situation

You can find links to both, below in my signatures.

 

[awoohaw]

Try these:

Assuming you want to put the new looked up value in cell Sheet1!B1

=XLOOKUP(A1,Sheet2!B1:B3000,Sheet2!C1:C3000)

if you don't have 365 (Update your profile so the forum knows your version please)

=VLOOKUP(A1,Sheet2!B1:C3000,2,0)

 

[Arinoum]

Need more clarity on how numbers change in Column C?

Further to give you an optimal solution would need few things as below -

# Update your current Excel Version and platform

# Share an XL2BB of actual data to understand the complexity of the situation

You can find links to both, below in my signatures.

My apologies, I'm finding it hard to explain. Below is the sheet. I am adding column A and I want the information from Column A of sheet 2 to populate in column A of sheet 1.

Cost Code (this exists in sheet one and I would like it to populate here so the formula would go here)	User Name
=if(user name column rage sheet 1 = user name column range sheet 2, return value from sheet 2 column A)

 

[awoohaw]

You cant have a value in column A and then want to put a formula in column A.
What column contains "user name" on sheet 1?
If it is not column A, then the formulas in Post #2 will work you just need to change the references.
And, what is also important, you have not said what version of excel you are using?

Cells in Sheet1 Column A, beginning with A2

=XLOOKUP("sheet 1 user name column row 2", "sheet 2 user name column", "sheet 2 logon ID column")

 

[Arinoum]

You cant have a value in column A and then want to put a formula in column A.
What column contains "user name" on sheet 1?
If it is not column A, then the formulas in Post #2 will work you just need to change the references.
And, what is also important, you have not said what version of excel you are using?

Cells in Sheet1 Column A, beginning with A2

=XLOOKUP("sheet 1 user name column row 2", "sheet 2 user name column", "sheet 2 logon ID column")

Thank you so much!

 

[awoohaw]

You're welcome.

Best wishes!

 

[Arinoum]

You're welcome.

Best wishes!

One more question if you don't mind. I'm trying to do the same but there are 3 criteria. Name, Machine ID and Hosted ID which are unique numbers that do not repeat. The names repeat if the name is associated with multiple Machine IDs and Hosts. I used the same formula you gave me and the result is matching the name to the first machine ID number and it repeats that number each time the same name appears. My sheets has the same name but different IDs. Not sure how to do this. Can I do something like =match name from sheet 1 to name from sheet 2 and return result of Machine ID and repeat it however many times the machine ID differs and appears. I should add that both ID numbers are unique meaning if Machine ID is W22 and Hosted ID is Q11 that's the only time those 2 numbers appear and they are only associated with one another.

Name	Machine ID	Hosted ID

 

[awoohaw]

Please post some data?

 

[Arinoum]

Sure. Sheet1 has a column with names. I'm trying to add to it information from sheet 2 that has a column for names, Machine ID and Hosted ID. I wanted to do a simple IF names match then return value in B and C2 however Sheet 1 has other details and the names repeat more than in sheet 2 due to the information listed in other columns. I would like to keep the other column information the same I am just looking to add 2 more columns with Machine and Hosted ID matching the names. Thank you so much for your help!

Names	Machine ID	Hosted ID
Green, John	W11	Q1
Green, John	W33	Q5
Green, John	G54	R4
Green, John	H55	Q3
Marks, Heather	W88	S01
Marks, Heather	W90	Q22
Andrews, Joe	E34	W12
Andrews, Joe	F19	R04
Andrews, Joe	W10	R6