ExcelBanter - If and Lookup

ExcelBanter (https://www.excelbanter.com/)

- Excel Discussion (Misc queries) (https://www.excelbanter.com/excel-discussion-misc-queries/)

- - If and Lookup (https://www.excelbanter.com/excel-discussion-misc-queries/246142-if-lookup.html)

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
--
KS

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

On Oct 22, 12:06*am, KS wrote:
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
--
KS

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

"KS" wrote:

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
--
KS