JavaScript is disabled. For a better experience, please enable JavaScript in your browser before proceeding.
You are using an out of date browser. It may not display this or other websites correctly.
You should upgrade or use an
alternative browser .
If and Lookup
Thread starter Thread starter
KS
Start date Start date
Oct 21, 2009
How can I do the following
If 'Sheet 1'A1:A20 = 'Sheet2'A1:A20 then return value from 'Sheet2'D1
20
into 'Sheet1' D1
20
Select Sheet1 D1
20
now enter this formula
=IF(AND(A1:A20=Sheet2!A1:A20),Sheet2!D1
20,"")
not just enter, use Ctrl + Shift + Enter
Hi,
Welcome to the world of array formula. You must follow the instruction on
how to enter the formula precisely.
Select D1 to D20 on sheet 1 and then paste this formula into the formula bar.
=IF(COUNTIF(A1:A20,Sheet2!A1:A20)=20,Sheet2!D1
20,"")
Now very important hold down CTRL & Shift keys and tap enter. If you've done
it correctly then Excel will have put curly brackets around the formula {}.
Mike
20