If and Lookup

K

KS

How can I do the following

If 'Sheet 1'A1:A20 = 'Sheet2'A1:A20 then return value from 'Sheet2'D1:D20
into 'Sheet1' D1:D20
 
M

muddan madhu

Select Sheet1 D1:D20

now enter this formula
=IF(AND(A1:A20=Sheet2!A1:A20),Sheet2!D1:D20,"")

not just enter, use Ctrl + Shift + Enter
 
M

Mike H

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:D20,"")

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
 
Top