SOLVED

IF THEN WHERE statement?

%3CLINGO-SUB%20id%3D%22lingo-sub-2729442%22%20slang%3D%22en-US%22%3EIF%20THEN%20WHERE%20statement%3F%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2729442%22%20slang%3D%22en-US%22%3E%3CP%3EI%20have%20two%20sheets%20in%20a%20workbook.%20I%20need%20to%20set%20a%20cell's%20value%20where%20the%20ID%20on%20one%20sheet%20equals%20the%20ID%20on%20a%20different%20sheet.%20But%20there's%20not%20always%20going%20to%20be%20a%20match%2C%20they%20don't%20line%20up%20equally.%26nbsp%3B%20Is%20there%20a%20way%20where%20Excel%20looks%20for%20the%20value%20within%20an%20entire%20column%2C%20and%20if%20it%20finds%20one%2C%20it%20grabs%20the%20value%20in%20the%20other%20cell.%20(Excel%20365%20on%20Windows%2010)%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ESo%3A%20set%20sheet1%3AB2%20to%20sheet2%3AVendorID.%3F%20where%20sheet1.AgentID%20%3D%20sheet2.VenderCode%3C%2FP%3E%3CP%3ECan%20that%20be%20done%3F%3C%2FP%3E%3CP%3ESheet%201%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22sheet1.png%22%20style%3D%22width%3A%20146px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F308576iBEDF5F404C343247%2Fimage-size%2Flarge%3Fv%3Dv2%26amp%3Bpx%3D999%22%20role%3D%22button%22%20title%3D%22sheet1.png%22%20alt%3D%22sheet1.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ESheet%202%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22sheet2.png%22%20style%3D%22width%3A%20203px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F308578iF149E4BCA3DC2BBB%2Fimage-dimensions%2F203x276%3Fv%3Dv2%22%20width%3D%22203%22%20height%3D%22276%22%20role%3D%22button%22%20title%3D%22sheet2.png%22%20alt%3D%22sheet2.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-2729442%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2729465%22%20slang%3D%22en-US%22%3ERe%3A%20IF%20THEN%20WHERE%20statement%3F%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2729465%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F1146079%22%20target%3D%22_blank%22%3E%40EK_Blackwell%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EPlease%20consider%20convert%20to%20number%20the%20ID%20values%3C%2FP%3E%3CPRE%20class%3D%22lia-code-sample%20language-excel-formula%22%3E%3CCODE%3E%3DINDEX(Sheet2!A2%3AA17%2CMATCH(A2%2CSheet2!B2%3AB17%2C0)%2C1)%3C%2FCODE%3E%3C%2FPRE%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2729507%22%20slang%3D%22en-US%22%3ERe%3A%20IF%20THEN%20WHERE%20statement%3F%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2729507%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F1146079%22%20target%3D%22_blank%22%3E%40EK_Blackwell%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CEM%3EI%20have%20two%20sheets%20in%20a%20workbook.%20I%20need%20to%20set%20a%20cell's%20value%20where%20the%20ID%20on%20one%20sheet%20equals%20the%20ID%20on%20a%20different%20sheet.%20But%20there's%20not%20always%20going%20to%20be%20a%20match%2C%20they%20don't%20line%20up%20equally.%26nbsp%3B%20Is%20there%20a%20way%20where%20Excel%20looks%20for%20the%20value%20within%20an%20entire%20column%2C%20and%20if%20it%20finds%20one%2C%20it%20grabs%20the%20value%20in%20the%20other%20cell.%20(Excel%20365%20on%20Windows%2010)%3C%2FEM%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CEM%3ESo%3A%20set%20sheet1%3AB2%20to%20sheet2%3AVendorID.%3F%20where%20sheet1.AgentID%20%3D%20sheet2.VenderCode%3C%2FEM%3E%3C%2FP%3E%3CP%3E%3CEM%3ECan%20that%20be%20done%3F%3C%2FEM%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EYes%2C%20it%20can%20be%20done%20quite%20easily.%26nbsp%3B%3C%2FP%3E%3CP%3EIn%20your%20sheet%201%2C%20whatever%20column%20you%20wish%20to%20enter%20it%3C%2FP%3E%3CP%3E%3CSTRONG%3E%3DINDEX(Sheet2!A2%3AA18%2CMATCH(Sheet1!A2%2CSheet2!B2%3AB18%2C0))%3C%2FSTRONG%3E%3C%2FP%3E%3CP%3ENote%2C%20I%20just%20entered%20through%20row%2018%20because%20that's%20how%20far%20your%20image%20extended%3B%20those%20two%20references%20to%20A2%3AA19%20and%20B2%3AB19%20should%20reflect%20the%20actual%20size%20the%20data%20array%20in%20your%20own%20Sheet2)%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2729514%22%20slang%3D%22en-US%22%3ERe%3A%20IF%20THEN%20WHERE%20statement%3F%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2729514%22%20slang%3D%22en-US%22%3E%3DXLOOKUP(A2%2CSheet2!B%3AB%2CSheet2!A%3AA)%3CBR%20%2F%3EWill%20work%20also.%20Just%20drag%20down.%3CBR%20%2F%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2730499%22%20slang%3D%22en-US%22%3ERe%3A%20IF%20THEN%20WHERE%20statement%3F%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2730499%22%20slang%3D%22en-US%22%3EDidn't%20know%20this!%20I'll%20actually%20start%20using%20this%20in%20my%20day-to-day%20work%2C%20thanks%20for%20explaining!%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2733524%22%20slang%3D%22en-US%22%3ERe%3A%20IF%20THEN%20WHERE%20statement%3F%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2733524%22%20slang%3D%22en-US%22%3E%3CP%3EEDIT%3A%20I%20figured%20out%20what%20I%20did%20wrong%20and%20the%20formula%20is%20working.%20Thank%20you!%3C%2FP%3E%3C%2FLINGO-BODY%3E
New Contributor

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

sheet1.png

 

Sheet 2

sheet2.png

10 Replies

@EK_Blackwell 

 

Please consider convert to number the ID values

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

 

@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)

=XLOOKUP(A2,Sheet2!B:B,Sheet2!A:A)
Will work also. Just drag down.
best response confirmed by EK_Blackwell (New Contributor)
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")
Didn't know this! I'll actually start using this in my day-to-day work, thanks for explaining!

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

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

Thank you, everyone! The formula is working!
Anytime
I would like to ask you please to flag the post as answered.

@EK_Blackwell , glad it helped