How to search for one value from data available in multiple columns and rows?

Fahad S

So i have one field where i need my team to input purchase order number (Correct PO number (unit team to update)). Once they put it there, i want to put a formula in next column that acts as a validation check to see if the number inserted matches or does not match with purchase order numbers in another sheet.

So the formula should pickup (Correct PO number (unit team to update)) above and match it against ALL cells in ALL columns (24) and ALL rows (~20,000) in another sheet (below) to come back with saying "OK" if the purchase order number exists in the other sheet (below) or "NOT OKAY" if purchase order number does not exist in this other sheet (below)

I tried IF formula but failed. Would really appreciate help here. the reason why i did not put all POs above in one column is because of 20,000 lines limitation since the PO numbers i have are 450K+ that i need the formula to check from. Thanks.

Paul Newcome

You would use a COUNTIFS and the cross sheet reference for your range would be all cells. Then you can nest that in an IF statement to say that if the count is more than zero, output what you want for a match, otherwise output what you want for no match.

=IF(COUNTIFS({Full Sheet Range}, @cell = [Correct PO number (unit team to update)]@row) > 0, "good", "bad")