Number searching using MATCH funtion?

[hcousino]

Hello all,

I am currently trying to search an array of random numbers to verify that a specific number is present. When I use the match function it only seems to work with a columb of numbers and not a whole array. Here is an example of what I am trying to do but its not working.... Since 1 is present in the array I would expect the function to return Yes but insteat is returns NA.

 

[herofox]

upload excel file please ?!!!

 

[hcousino]

Here is the file its just an example to demonstrate what I am trying to do. Basically I am expecting the response in cell B1 to be yes because 1 is present in the array of numbers. Thanks!

 

[herofox]

ok you can use this

=IFERROR(IF(VLOOKUP($A1,$D$7:$G$10,1,0)=$A1,"Yes"),"No")

Match , I think can only work with vertical data, not horizontal in your case

 

[hcousino]

Thank you so much! Yes, I was unsure if MATCH was the correct function to use. I appreciate your help!

 

[vletm]

hcousino
You could reread Match-function's syntax eg:

MATCH function - Microsoft Support

How to use the MATCH function in Excel to search for a specified item in a range of cells, returning the relative position of that item in the range.

support.microsoft.com

Especially those examples.
... and if You have 365-version, then there are more to read.

 

[p45cal]

you could try:

=IF(SUM(--(A1=$E$7:$H$10)),"Yes","No")

or

=IF(SUMPRODUCT(--(A1=$E$7:$H$10)),"Yes","No")

depending on your version of Excel.

 

[bosco_yip]

Try,

B1, formula copied down :

=IF(COUNTIF(E$7:H$10,A1),"Yes","No")

or,

=IF(SUMIF(E$7:H$10,A1),"Yes","No")