[Help] Basic Excel query

[kevo]

Hello NSC Excel bods. Can anyone help with the following?

I need to reference a value in a formula. But rather than having the actual text in the formula I need it to refer to a value in a list. As cells in the data can contain multiple values, I need to specify that the cell contains the text rather than being an exact match.

So say if my list is:

A2 Apples
A3 Oranges
A4 Pears

As part of my formula, I would normally (for apples) put "*Apples*" to return all cells that contain the word apples.

However if I want to refrence the cell in the list instead, *$A$2* doesn't work.

Using asterisks to denote 'contains' doesnt work when referencing a value in a cell.

(For an exact match "Apples" and $A$2 both of course work).

How can I specify 'contains' when using a cell reference rather than the actual text in the formula?

 

[SeagullsoverLondon]

You need to name the cell ranges
There is a name box in left hand corner with cell reference é.g A2 which you can rename

Sent from my moto g(7) power using Tapatalk

 

[KZNSeagull]

Have you tried concatenation in the formula ie "*"&$A$2&"*" ?

Edit: Just tried this with CountIf. It works

 

[kevo]

Have you tried concatenation in the formula ie "*"&$A$2&"*" ?

Edit: Just tried this with CountIf. It works

Thanks but that returns everything containing a value for me (same count as "*") not just items containing that particular value.

Edit: It did work but only when I defined the cell as suggested by [MENTION=42377]SeagullsoverLondon[/MENTION] above.

Thanks both! It was doing my head in!

 

[KZNSeagull]

Thanks but that returns everything containing a value for me (same count as "*") not just items containing that particular value.

Edit: It did work but only when I defined the cell as suggested by [MENTION=42377]SeagullsoverLondon[/MENTION] above.

Thanks both! It was doing my head in!

Well I have done it:

a1 Apples and Pears
a2 Pears
a3 Apples

=COUNTIF(A1:A3,"=*"&A3&"*"). Returns 2. Is that not what you want?

 

[Seagulls over Essex]

Have you tried using vegetables instead of fruit?

 

[kevo]

Well I have done it:

a1 Apples and Pears
a2 Pears
a3 Apples

=COUNTIF(A1:A3,"=*"&A3&"*"). Returns 2. Is that not what you want?

Yes but it asnt working for me for some reason until I defined the cell. Don't worry, I was probably just doing something wrong. Thanks again.

 

[clapham_gull]

Learn the inbuilt power query and never go near most formulas ever again.





Sent from my SM-A526B using Tapatalk

 

[maffew]

Look up =SEARCH function which allows wild cards and case insensitivity, works like a character index.
Also check =FIND
You shouldn't need to use named ranges for this

 

[zefarelly]

Have you tried using vegetables instead of fruit?

Depending on location, pork based references compute well

I've never had any problems calculating bacon, some people like x* hot sauasage.

 

[maffew]

https://www.ablebits.com/office-addins-blog/2015/10/07/excel-find-search-functions/

Yeah here you go.. strings and sub strings within a string

 

[maffew]

=ISNUMBER(SEARCH(substring,text))

 

[Iggle Piggle]

10 Print "ORANGES AND APPLES"
20 GOTO 10