Lookup 2/1 works on one line and not on others

[Eurekaonide]

Hi
I have the below formula and something strange is happening. It works on one line but when I copy it down it just #N/A errors. Ive check that the cell formats are all general. they are not numbers they are a mix of text and numbers. Ive tried simple vlookups and they find the values one by one so I am baffled as to why its not working? any ideas please?

=LOOKUP(2,1/(('Forecasts'!$A$4:$A$900=$A4)*('Forecasts'!$J$4:$J$900=$J4)*('Forecasts'!$O$4:$O$900=$O4)),"OK")

or suggestions on a better way?
I am trying to check if each row in one sheet has a corresponding row in another sheet (Forecasts) as there maybe multiples of each item I need to check the 3 elements are all in the same row to be a match.

 

[Phuoc]

Use COUNTIFS:

=IF(COUNTIFS(Forecasts!$A$4:$A$900,$A4,Forecasts!$J$4:$J$900,$J4,Forecasts!$O$4:$O$900,$O4),"OK","")

 

[Fluff]

What version of Excel are you using?

I suggest that you update your Account details (or click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)

 

[pitchoute]

Can you try one of these and share the result?

=IF(COUNTIFS('Forecasts'!$A$4:$A$900,$A4,'Forecasts'!$J$4:$J$900,$J4,'Forecasts'!$O$4:$O$900,$O4)>0,"OK","Not Found")

=IF(SUMPRODUCT(('Forecasts'!$A$4:$A$900=$A4)*('Forecasts'!$J$4:$J$900=$J4)*('Forecasts'!$O$4:$O$900=$O4))>0,"OK","Not Found")

 

[Eurekaonide]

What version of Excel are you using?

I suggest that you update your Account details (or click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)

My versions change from role to role but i believe its 365 that i am on now

 

[Fluff]

i believe its 365 that i am on now

In that case please update your profile. Thanks

Try the formula that Phuoc suggested in post#2