IF THEN WHERE statement?

[EK_Blackwell]

I have two sheets in a workbook. I need to set a cell's value where the ID on one sheet equals the ID on a different sheet. But there's not always going to be a match, they don't line up equally.  Is there a way where Excel looks for the value within an entire column, and if it finds one, it grabs the value in the other cell. (Excel 365 on Windows 10)

So: set sheet1:B2 to sheet2:VendorID.? where sheet1.AgentID = sheet2.VenderCode

Can that be done?

Sheet 1

Sheet 2

[#2]

@EK_Blackwell 

Please consider convert to number the ID values

=INDEX(Sheet2!A2:A17,MATCH(A2,Sheet2!B2:B17,0),1)

[#3]

@EK_Blackwell 

I have two sheets in a workbook. I need to set a cell's value where the ID on one sheet equals the ID on a different sheet. But there's not always going to be a match, they don't line up equally.  Is there a way where Excel looks for the value within an entire column, and if it finds one, it grabs the value in the other cell. (Excel 365 on Windows 10)

So: set sheet1:B2 to sheet2:VendorID.? where sheet1.AgentID = sheet2.VenderCode

Can that be done?

Yes, it can be done quite easily. 

In your sheet 1, whatever column you wish to enter it

=INDEX(Sheet2!A2:A18,MATCH(Sheet1!A2,Sheet2!B2:B18,0))

Note, I just entered through row 18 because that's how far your image extended; those two references to A2:A19 and B2:B19 should reflect the actual size the data array in your own Sheet2)

[#4]

=XLOOKUP(A2,Sheet2!B:B,Sheet2!A:A)
Will work also. Just drag down.

[#5 · Accepted solution]

@DKoontz 

As a comment, with XLOOKUP we may return entire spill at once

=XLOOKUP(A2:A10,Sheet2!B2:B100,Sheet2!A2:A100, "no such")

[#6]

Didn't know this! I'll actually start using this in my day-to-day work, thanks for explaining!

[#7]

Thanks! I entered it and I got 0 back as a value. I'm trying to figure out why because there's an entry it should have picked up

[#8]

EDIT: I figured out what I did wrong and the formula is working. Thank you!

[#9]

Thank you, everyone! The formula is working!

[#10]

Anytime
I would like to ask you please to flag the post as answered.

[#11]

@EK_Blackwell , glad it helped