Urgent Help required IF/ISERROR/MATCH

[aaromic2000]

I'm trying to compare four cells from sheet 1 with four columns in sheet 2. Based on which I'm trying to find the missing numbers. Why this formula is not working? Any suggestions please.

=IF(ISERROR(MATCH(B2&E2&K2&M2,Sheet2!B:B&Sheet2!C:C&Sheet2!G:G&Sheet2!D:D,0)),"Missing","Found")

Thanks

 

[CharlesChuckieCharles]

why not use countif

=COUNTIF(Column,Cell)

=COUNTIF(Sheet2!B1:B16,Sheet1!A1)

 

[XOR LX]

Hi,

As it stands, that formula will need to be entered as an array formula**, though I would strongly recommend that you do not use entire columns for the range references in that case, e.g.:

=IF(ISERROR(MATCH(B2&E2&K2&M2,Sheet2!B1:B1000&Sheet2!C1:C1000&Sheet2!G1:G1000&Sheet2!D1:D1000,0)),"Missing","Found")

An alternative, non-array version, is:

=IF(ISERROR(MATCH(B2&E2&K2&M2,INDEX(Sheet2!B1:B1000&Sheet2!C1:C1000&Sheet2!G1:G1000&Sheet2!D1:D1000,,),0)),"Missing","Found")

Regards


**Array formulas are not entered in the same way as 'standard' formulas. Instead of pressing just ENTER, you first hold down CTRL and SHIFT, and only then press ENTER. If you've done it correctly, you'll notice Excel puts curly brackets {} around the formula (though do not attempt to manually insert these yourself).</SPAN></SPAN>

 

[aaromic2000]

why not use countif

=COUNTIF(Column,Cell)

=COUNTIF(Sheet2!B1:B16,Sheet1!A1)

Hi Charles,

I've taken alternate route. I did Concatenate the 4 columns in the both the sheet and did a simple MATCH.


Thanks for your support </SPAN>
Concatenate </SPAN>