Hello,



I need to find a way to do a Boolean search on a single row to see if specific text is present in any cells within that row. However, the row to search is dependent on the text in its first cell. Also, other than the first column of my table, all the other cells cannot be organized in any particular way because the traits to be listed in each are not regular in any way from row to row.

As an example, let’s say we have the following table:

Plates Red Ceramic
Bowls Wood Green Blue
Cups White Plastic


I want to be able to search the cups row to see if ‘White’ is present by just typing ‘cups’ in one cell (let’s say, cell B10) and ‘white’ in another cell (C10). The question is, what would my formula look like In my result cell for this?

So far, I’ve got:
=MATCH(B10,A:A,0) which gives me the right row to look (3 in this case)
-and-
=MATCH(C10,3:3,0) which would let me know if it sees ‘White’ or not

The issue is, I don’t know how to put the results of the first formula into the second argument of the second formula. I can’t manually type ‘3:3’. I need it to pull the ‘3’ result from the first formula and insert it into the second formula automatically. How can I do this, or is there another way I should be going about this problem?

Any help would be greatly appreciated.

Maybe:

=ISNUMBER(MATCH(C10,OFFSET($A$1,MATCH(B10,A:A,0)-1,,,10),0))

Replace 10 with the required number of columns to search.

In case you/user enter(s) data into your table, then it is poorly designed - and as result you have difficulties to make any calculations based on this table.

In attachment is an alternative.

On sheet DataTable user enters parameters for item - a single parameter on row. In case the user enters some parameter twice, the entry is blocked and a message pops up (Data Validation). (Data Validation formula uses Names, defined in FORMULAS>Name Manager.)

On sheet PivotTable a Pivot Table report is designed, which is an analog of your table.

Attached Files

example.xlsx (13.8 KB, 8 views)

ArviLaanemets: Thank you for the suggestion, and for the point about design. I completely understand your poor design comment, but in this case, your solution will not work for me. I am creating a tool for myself to use, so I don't mind it being a little clunky as I don't have to worry about user error.

Xor, this is exactly what I needed, and it works perfectly! Thank you so much! I never would have thought of using OFFSET.

Thanks again to both of you for your responses!