I have this file,:
ID Barcode
1 11
2 13
3 14
4 15
5 16
And I would like to do a logic function to know if the barcode values were recorded, for example, I enter the value 18 in another worksheet for conference, and the answer will be not found or I enter the value 11 and the answer will be found.
I tried this:
IF(C1=B2:B6,"FOUND","NOT FOUND")
But doesn't work. Is it possible to this in excel?
So, use iferror() with index() and match(). Enter the value you are looking for in cell C1 and cell C4 returns either not found or the ID number.
=IFERROR(INDEX($A$2:$A$6,MATCH(C1,$B$2:$B$6,0)),"Not Found")
See
For example use:
=IF(ISNUMBER(MATCH(D2,$B$2:$B$6,0)),"FOUND","NOT FOUND")
Or, if barcodes are unique:
=CHOOSE(COUNTIF($B$2:$B$6,D2)+1,"NOT FOUND","FOUND")
If returning TRUE or FALSE is good enough for you just use:
=COUNTIF($B$2:$B$6,D2)>0
If your goal is to retrieve the ID when Barcode is found, then @SolarMike his suggestion with INDEX() and MATCH() is the way to go for sure.
You are very close. Just missing an OR and entering it as an array formula.
Your logical test is returning an array of comparisons:
C1: 14 ==>
{FALSE;FALSE;TRUE;FALSE;FALSE}
But to determine if ANY of the returned values are TRUE, you need the OR function:
OR(C1=$B$2:$B$6)
Putting it all together, we have the ARRAY formula:
=IF(OR(C1=$B$2:$B$6),"Found","Not Found")
Since this is an array formula, you need to "confirm" it by holding down ctrl + shift while hitting enter. If you do this correctly, Excel will place braces {...} around the formula as observed in the formula bar
