EXCEL idiot

[beividepabs]

Hello !

I hope someone can help and excuse my ignorance.

I have tried to get a function working to sort out a problem I have.

I have a database that has a column (D) containing an amount in £.
I need to compare amount on column D from Sheet1 with all amounts from sheet 'expenses projects' (A2, A3, A4 onwards) and return "Project" if = and "NO Project" if not.

I have tried (possibly incorrectly) the below
=IF(ISNUMBER(MATCH(D2,'expenses projects'!A:A,0)),"Project","NO Project")

Can someone help?

Many thanks in advance

 

[GTO]

I'm not very much with formulas, but I believe that MATCH will balk at the array referring to the entire column. For instance, A1:A65000 seems to work fine.

 

[AlphaFrog]

The syntax of your formula is correct. It should return "Project" if it finds an exact match of D2.

Double check that the exact value of D2. If it's a calculated value, make sure there is not a rounding error or trailing decimal values e.g. 1.234 is not a match for 1.23. The values have to be an exact match.

This will round D2 to two decimal places if needed.
=IF(ISNUMBER(MATCH(ROUND(D2,2),'expenses projects'!A:A,0)),"Project","NO Project")

Or this would do the same thing using a COUNTIF function...
=IF(COUNTIF('expenses projects'!A:A,ROUND(D2,2)),"Project","NO Project")

 

[GTO]

My bad and thank you AlphaFrog. I'm not sure what I goobered when I put it in the first time.

 

[Peter_SSs]

Edit: Oops, I also didn't do very well. Didn't notice that AlphaFrog had already suggested possibly using COUNTIF.
However, my comments still might add something of use.

Apart from a numerical rounding issue is it possible that D2 is a number formatted as text and 'expenses projects'!A:A are actual numbers or vice-versa? That could also cause your formula to show "NO Project" when you thought it should show "Project".

If that is the case you could try this formula instead (it still won't help if the issue is a rounding one).

=IF(COUNTIF('expenses projects'!A:A,D2),"Project","NO Project")

I don't know how many projects you have in that column A but your formula would work more efficiently if you did restrict that range. If you don't know how many projects there are, or will be, in column A of 'expenses projects' but could, for example, say that there isn't going to be more than about 1,500 then you could change the formula to one of these

=IF(COUNTIF('expenses projects'!A$1:A$2000,D2),"Project","NO Project")

=IF(ISNUMBER(MATCH(D2,'expenses projects'!A$1:A$2000,0)),"Project","NO Project")