If a Cell text is a substring contained in another column Cell range, get other column val

**programm** · 12-24-2023, 10:02 PM

Hi and happy holidays..

Just want to try something that I cannot get going I tried around Match and Index but it won't work

This will explain more: in pic with example and attached sheet

**windknife** · 12-24-2023, 10:31 PM

One way,

Please Login or Register  to view this content.

**protonLeah** · 12-24-2023, 10:38 PM

Please Login or Register  to view this content.

**HansDouwe** · 12-24-2023, 10:46 PM

Another way:

Formula:

Please Login or Register  to view this content.

**programm** · 12-25-2023, 06:36 AM

Thanks all for the suggestion

Originally Posted by protonLeah

Please Login or Register  to view this content.

This does it also if I split the word like Or ange, that is great. Can it also be done if I type BigOrange or Big Orange, since Orange is contained? I tried with prefix but returns N/A
Also can it be based on the first column A that lists the number instead of the ROW. I noticed the column A that numbers them is missing

**HansDouwe** · 12-25-2023, 06:50 AM

If you type BigOrange it is not possible to find a match with oranges:
oranges does not contain BigOrange and visa versa BigOrange does not contain oranges.

**programm** · 12-25-2023, 01:16 PM

Oh this is actually fine. I will have a prefix in the list not in the input field. So if I have in the list BigOranges and I type Oranges or Or anges, that detects it, so it does what I needed thnx again for clarifying and for the code.

And even though the code =MATCH("*"&SUBSTITUTE(($Q$3)," ","")&"*",B2:B312,0)

does not show it uses column A that I had for numbers, it does take the number from the same row ignoring A1 that are the headers as if it makes ROW-1. So that is good as I get the number seen on the A column

If a Cell text is a substring contained in another column Cell range, get other column val

LinkBack

Thread Tools

Rate This Thread

Display

If a Cell text is a substring contained in another column Cell range, get other column val

Re: If a Cell text is a substring contained in another column Cell range, get other column

Re: If a Cell text is a substring contained in another column Cell range, get other column

Re: If a Cell text is a substring contained in another column Cell range, get other column

Re: If a Cell text is a substring contained in another column Cell range, get other column

Re: If a Cell text is a substring contained in another column Cell range, get other column

Re: If a Cell text is a substring contained in another column Cell range, get other column

Thread Information

Users Browsing this Thread

Similar Threads

[SOLVED] Check what specific text is contained in cells, then sum numbers contained in those cell

Checking that a cell contains a substring when assessing a column

Look up Latest Dates from a Table Column based on substring contained in another column

[SOLVED] Cond. formatting a column/Range, highlight cell in column/range if equal to specific text

Search for cell value as substring in column and return matched cell value

Replacing the column no. of a link by a column no. contained in another cell

If text mathces column 1 range, display value in column 2 cell

Bookmarks

Bookmarks

Posting Permissions