SOLVED

Excel formula help

Copper 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.

best response confirmed by jasonm69 (Copper Contributor)
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!
1 best response

Accepted Solutions
best response confirmed by jasonm69 (Copper Contributor)
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.

View solution in original post