Referencing multiple cells based on a single cell input

%3CLINGO-SUB%20id%3D%22lingo-sub-1266169%22%20slang%3D%22en-US%22%3EReferencing%20multiple%20cells%20based%20on%20a%20single%20cell%20input%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1266169%22%20slang%3D%22en-US%22%3E%3CP%3EThis%20is%20really%20poor%20wording%2C%20but%20I'm%20looking%20for%20a%20way%20to%20automatically%20reference%20multiple%20cells%20based%20on%20the%20input%20of%20a%20single%20cell.%20I%20have%20two%20sheets%20in%20my%20excel%20work%20book%2C%20sheet%20one%20will%20have%20all%20the%20manual%20entered%20data%2C%20sheet%20two%20will%20be%20a%20permanent%20reference.%20I%20want%20to%20be%20able%20to%20type%20a%204%20digit%20%22Code%22%20into%20one%20cell%20and%20have%20it%20fill%20the%20name%2C%20and%20address%20of%20the%20cells%20from%20the%20second%20sheet.%20I%20have%20the%20cell%20I%20want%20to%20input%20shown%20on%20the%20sheet%201%20with%20a%20red%20arrow%2C%20and%20the%20cells%20I%20want%20to%20have%20automatically%20input%20shown%20with%20black%20arrows.%20sheet%20two%20is%20the%20cells%20that%20contain%20all%20the%20data%20that%20I%20want%20transferred%20to%20the%20cells%20in%20sheet%20one.%20Thanks%20for%20any%20help%2C%20this%20should%20be%20stupid%20easy%2C%20I'm%20just%20drawing%20a%20blank.%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-1266169%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1266216%22%20slang%3D%22en-US%22%3ERe%3A%20Referencing%20multiple%20cells%20based%20on%20a%20single%20cell%20input%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1266216%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F601746%22%20target%3D%22_blank%22%3E%4077F150XLT%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThere's%20a%20few%20ways%20to%20do%20it.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EHere's%20XLOOKUP.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1269569%22%20slang%3D%22en-US%22%3ERe%3A%20Referencing%20multiple%20cells%20based%20on%20a%20single%20cell%20input%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1269569%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F428790%22%20target%3D%22_blank%22%3E%40Patrick2788%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3EUnfortunately%20i'm%20on%20the%20semi%20annual%20update%20plan%20with%20office%20365%20so%20I%20don't%20have%20access%20to%20the%20xlookup%20function%20yet.%20is%20there%20another%20way%20to%20do%20it%3F%20Thanks%20a%20bunch.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1271503%22%20slang%3D%22en-US%22%3ERe%3A%20Referencing%20multiple%20cells%20based%20on%20a%20single%20cell%20input%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1271503%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F601746%22%20target%3D%22_blank%22%3E%4077F150XLT%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3EYes%2C%20your%20base%20formula%20(going%20off%20my%20sample%20workbook)%20becomes%3A%3C%2FP%3E%3CP%3E%3DIFERROR(VLOOKUP(%24A2%2CRetailer!%24A%3A%24F%2CCOLUMN(B1)%2C0)%2C%22%22)%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EFill%20handle%20it%20across%20and%20down%20as%20needed.%20Make%20the%20column%20references%20(%24A%3A%24F)%20specific%20to%20your%20range.%3C%2FP%3E%3C%2FLINGO-BODY%3E
Highlighted
New Contributor

This is really poor wording, but I'm looking for a way to automatically reference multiple cells based on the input of a single cell. I have two sheets in my excel work book, sheet one will have all the manual entered data, sheet two will be a permanent reference. I want to be able to type a 4 digit "Code" into one cell and have it fill the name, and address of the cells from the second sheet. I have the cell I want to input shown on the sheet 1 with a red arrow, and the cells I want to have automatically input shown with black arrows. sheet two is the cells that contain all the data that I want transferred to the cells in sheet one. Thanks for any help, this should be stupid easy, I'm just drawing a blank. 

3 Replies
Highlighted

@77F150XLT 

There's a few ways to do it.

 

Here's XLOOKUP.

Highlighted

@Patrick2788 

Unfortunately i'm on the semi annual update plan with office 365 so I don't have access to the xlookup function yet. is there another way to do it? Thanks a bunch.

Highlighted

@77F150XLT 

Yes, your base formula (going off my sample workbook) becomes:

=IFERROR(VLOOKUP($A2,Retailer!$A:$F,COLUMN(B1),0),"")

 

Fill handle it across and down as needed. Make the column references ($A:$F) specific to your range.