Function, or Group of Functions, That Allow You to Search a Dynamic Table?

[CAStig1997]

I was wondering if there was a simpler way of searching for the value that can appear in multiple cells depending on user input? 

Under normal circumstances two "Blocking" wouldn't exist, but for the purpose of the example I have two to show how it can appear in multiple different locations. 

Usually I make Index-Match to search for a specific value by associating it with a number, but in this circumstance it isn't possible for me to know where "Blocking" is going to show up. I also can't set up a Lookup Array for Match in both Index's Row and Column. (=INDEX(A5:D9,MATCH("Blocking",?,0),MATCH("Blocking",?,0))

Vlookup and Hlookup I have a similar issue with because I have to know the Row or Column it could show up in. (=Vlookup("Blocking",A5:D9,?)

I've also tried Lookup + OR (=Lookup("Blocking",OR(A5-A9,B5-B9,C5-C9,D5-D9)) but all it returns is an error or zero.

I could do it all with IFS or IF + OR but if I make a mistake its going to be rough finding where it went wrong and I'd have to do this 5 times across 16 cells each in other locations since I have other Tables that require the same method. 

If I can find a way to search through those Cells for "Blocking" then I can setup a helper cell that records the result and allow me to use the function: IF(A3="Blocking",20,10)

[#2]

@CAStig1997 

One option would be to use Conditional Formatting > Highlight Cells Rules > Highlight Duplicates... to quickly determine if there are duplicates in the range.

If you'd like a formula, =COUNTIF(A5:D9,"Blocking") will return the number of cells that contain "Blocking".

And if you want to know the addresses of these cells:

=TEXTJOIN(",",TRUE,IFERROR(ADDRESS((A5:D9="Blocking")*(ROW(A5:D9)),(A5:D9="Blocking")*(COLUMN(A5:D9))),""))

[#3]

@Hans Vogelaar That isn't quite what I'm looking for. The cells in the picture are just an example. How it actually would look is with the image I'll provide. 

There can be many things selected from the drop down box, however, Blocking has a unique property in that it would give a number value if someone were to select it. 

I'm trying to automate the process by having the worksheet go through these cells and identify if Blocking exists or not. If Blocking exists then the number value will be applied, if it doesn't find Blocking in any of these cells then there isn't a number value applied.

The issue is getting it to search Rows and Columns because Blocking could be in the first cell, middle, or the last cell depending on what is in previous cells.

The closest thing I have to something viable is this: 

All the 0s represent the formula: =IFERROR(IF(LOOKUP(A347,E8:K8)=A347,1,0),0)

*A347 is "Blocking"

All those cells are checking for the word "Blocking" to show up in Cells A8:D11,A32:D35, and Etc. 1 means Blocking was found, 0 means Blocking was not found

All the 20s represent the Formula: =IF(LOOKUP(1,B347:B350)=1,19,20)

If a 1 is found, which means Blocking was found, then it becomes a 19, otherwise it is a 20

The last group of 20 represent the Formula: =IFERROR(IF(LOOKUP(19,C347:C366)=19,"19-20",20),20)

If a 19 is found then I get my value of "19-20", if it isn't it stays 20 and 20 becomes the number. And yes "19-20" as text is on purpose because a number doesn't have to be the value.

I'm trying to cut all this down and trying to see if there's a way to just tell the sheet to look through these 4 rows and these 4 columns to find out if the term "Blocking" exists. Then I'd only need to do one extra Formula that will provide the numerical answer. Because Blocking just has to come back as TRUE or FALSE and then I'm set.

[#4 · Accepted solution]

@CAStig1997 

=IF(COUNTIF(A5:D9,"Blocking"),20,10)

[#5]

That's worked. I just never thought of it because I have never had ranges like that work with any other Function I've done in the past.