Microsoft Office Forums

Go Back   Microsoft Office Forums > >

Reply
 
Thread Tools Display Modes
  #1  
Old 02-20-2018, 04:36 PM
Kennebraun Kennebraun is offline Conditional Row Searching Mac OS X Conditional Row Searching Office 2016 for Mac
Novice
Conditional Row Searching
 
Join Date: Feb 2018
Posts: 2
Kennebraun is on a distinguished road
Default Conditional Row Searching

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.
Reply With Quote
  #2  
Old 02-20-2018, 10:56 PM
xor xor is offline Conditional Row Searching Windows 10 Conditional Row Searching Office 2016
Expert
 
Join Date: Oct 2015
Posts: 1,081
xor is a glorious beacon of lightxor is a glorious beacon of lightxor is a glorious beacon of lightxor is a glorious beacon of lightxor is a glorious beacon of lightxor is a glorious beacon of light
Default

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.
Reply With Quote
  #3  
Old 02-21-2018, 12:36 AM
ArviLaanemets ArviLaanemets is offline Conditional Row Searching Windows 8 Conditional Row Searching Office 2016
Expert
 
Join Date: May 2017
Posts: 749
ArviLaanemets is a splendid one to beholdArviLaanemets is a splendid one to beholdArviLaanemets is a splendid one to beholdArviLaanemets is a splendid one to beholdArviLaanemets is a splendid one to beholdArviLaanemets is a splendid one to beholdArviLaanemets is a splendid one to behold
Default

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
File Type: xlsx example.xlsx (13.8 KB, 8 views)
Reply With Quote
  #4  
Old 02-21-2018, 11:02 PM
Kennebraun Kennebraun is offline Conditional Row Searching Mac OS X Conditional Row Searching Office 2016 for Mac
Novice
Conditional Row Searching
 
Join Date: Feb 2018
Posts: 2
Kennebraun is on a distinguished road
Default

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!
Reply With Quote
Reply

Thread Tools
Display Modes


Similar Threads
Thread Thread Starter Forum Replies Last Post
Searching among hyperlinks abc3132 Excel 5 01-15-2018 06:03 AM
Conditional Row Searching Searching for Dupes Gef Excel 7 03-02-2015 12:48 PM
Searching for string on a formula AMD2800 Excel 1 12-17-2014 10:41 AM
Conditional Row Searching Searching hyperlinks PuddinPie Word 1 05-27-2011 02:02 AM
Conditional Row Searching effective searching mar999 Office 1 05-04-2011 04:51 AM

Other Forums: Access Forums

All times are GMT -7. The time now is 10:49 PM.


Powered by vBulletin® Version 3.8.11
Copyright ©2000 - 2022, vBulletin Solutions Inc.
Search Engine Optimisation provided by DragonByte SEO (Lite) - vBulletin Mods & Addons Copyright © 2022 DragonByte Technologies Ltd.
MSOfficeForums.com is not affiliated with Microsoft