SOLVED
Home

Excel formula help

%3CLINGO-SUB%20id%3D%22lingo-sub-358867%22%20slang%3D%22en-US%22%3EExcel%20formula%20help%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-358867%22%20slang%3D%22en-US%22%3E%3CP%3EI%20have%20a%20workbook%20with%20multiple%20sheets.%20I%20want%20to%20basically%20use%202%20columns%20worth%20of%20data%20from%20sheet%201%2C%20match%20it%20to%20the%20same%20data%20on%20sheet%202%2C%20and%20then%20return%20the%20value%20in%20a%20third%20column%20on%20sheet%202%2C%20in%20the%20neighbouring%20cell%20on%20sheet%201.%20I've%20tried%20many%20different%20equations%20without%20success.%20Can%20anyone%20help%20me%3F%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-358867%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-359706%22%20slang%3D%22en-US%22%3ERe%3A%20Excel%20formula%20help%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-359706%22%20slang%3D%22en-US%22%3EWelcome%2C%20Jason!%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-359690%22%20slang%3D%22en-US%22%3ERe%3A%20Excel%20formula%20help%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-359690%22%20slang%3D%22en-US%22%3EPerfect!%20Thank%20you%20so%20much%20for%20your%20time!%20%3A)%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-359682%22%20slang%3D%22en-US%22%3ERe%3A%20Excel%20formula%20help%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-359682%22%20slang%3D%22en-US%22%3EHello%20Jason%2C%3CBR%20%2F%3EThe%20formula%20in%20Sheet1!E2%2C%20copied%20down%20to%20Sheet1!E4%20is%3A%3CBR%20%2F%3E%3DLOOKUP(PI()%2C%3CBR%20%2F%3E1%2F((Sheet2!A2%3AA6%3D--F2)*(Sheet2!H2%3AH6%3DK2))%2C%3CBR%20%2F%3ESheet2!G2%3AG6)%3CBR%20%2F%3EF2%20is%20left-aligned%2C%20which%20indicates%20that%20Excel%20reads%20it%20as%20text.%20The%20double%20unary%20(--)%20before%20F2%20converts%20the%20text%20value%20of%20F2%20to%20number.%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-359654%22%20slang%3D%22en-US%22%3ERe%3A%20Excel%20formula%20help%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-359654%22%20slang%3D%22en-US%22%3E%3CP%3EHi%20Twifoo%3C%2FP%3E%3CP%3EI've%20attached%20below%20the%20example%20of%20data%20that%20I%20need%20to%20create%20this%20formula%20for.%20What%20I%20need%20is%20a%20formula%20that%20looks%20at%20cell%20F2%20%26amp%3B%20K2%20on%20sheets%201%2C%20finds%20the%20exact%20match%20on%20sheet%202%2C%20which%20in%20this%20example%20would%20be%20line%202%20on%20sheet%202.%20Then%20I%20need%20the%20formula%20to%20use%20the%20information%20in%20cell%20G2%20on%20sheet%202%20and%20put%20that%20data%20into%20cell%20E2%20on%20sheet%201.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20hope%20I%20have%20explained%20this%20in%20enough%20detail.%20Thanks%20for%20your%20help.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-358995%22%20slang%3D%22en-US%22%3ERe%3A%20Excel%20formula%20help%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-358995%22%20slang%3D%22en-US%22%3EPlease%20cite%20a%20specific%20example%20of%20your%20data%20and%20the%20result%20you%20need%20to%20achieve.%3C%2FLINGO-BODY%3E
jasonm69
New Contributor

I have a workbook with multiple sheets. I want to basically use 2 columns worth of data from sheet 1, match it to the same data on sheet 2, and then return the value in a third column on sheet 2, in the neighbouring cell on sheet 1. I've tried many different equations without success. Can anyone help me?

5 Replies
Please cite a specific example of your data and the result you need to achieve.

Hi Twifoo

I've attached below the example of data that I need to create this formula for. What I need is a formula that looks at cell F2 & K2 on sheets 1, finds the exact match on sheet 2, which in this example would be line 2 on sheet 2. Then I need the formula to use the information in cell G2 on sheet 2 and put that data into cell E2 on sheet 1.

 

I hope I have explained this in enough detail. Thanks for your help.

Solution
Hello Jason,
The formula in Sheet1!E2, copied down to Sheet1!E4 is:
=LOOKUP(PI(),
1/((Sheet2!A2:A6=--F2)*(Sheet2!H2:H6=K2)),
Sheet2!G2:G6)
F2 is left-aligned, which indicates that Excel reads it as text. The double unary (--) before F2 converts the text value of F2 to number.
Perfect! Thank you so much for your time! :)
Welcome, Jason!