named range referenced in formula

[bettercallanalysts]

Hi, I have a sheet with data points and another reference sheet with the different ways to write different body parts. The idea is to see which body parts/ appear in each data row, but because there are multiple ways to say the same body part, I have put the different variations of body part names into a list and made them into named ranges.


I now need to display the named range for each body part that shows up in each data row, but I'm stuck not knowing how to formulate this.


do you guys have any ideas?

 

[PhilS2520]

Hi bettercallanalysts,

Welxome to this forum.

Whaaat????

Your post is very UN-understandable. I can not visualize what you are trying to do. I read the words and I know what a named range is, and I know what a sheet is, but when I read the whole I can’t make anything out of it.

You say you made a named range out of body parts, so I am thinking you have all the possible body parts in one section (in a column row after row, one body part name in a cell) so all you need to do is something like:

=VLOOKUP(A1,NameOfBodyPartRange,2,<true false="">)
This looks up the value of A1 in the names range and returns the 2nd column value. You can use ISERROR() wrapper to make sure if the body part is in the list or not.</true>

 

[bettercallanalysts]

Hi PhilS250,
Sorry if it wasn't clear. Let me rephrase what I'm trying to do.

In a raw Data sheet I have a column C with sentences in it, all different sentences.
I also have in a 2nd sheet a matrix with a column A of words and a row 1 of other words (such as it creates a double entry table from A2/B1 to, say, L30 with empty cells in the middle) that I would like to match and count if these words appear in the sentences from col C.
The idea is to fill in the matrix by counting the number of times the words from column A and row 1 appears in all the sentences from col C combined.

The thing is that each cell from column A and row 1 need to have several distinct words. So I created a 3rd sheet with columns for each set of words I need in that cell, and gathering them into a named range (for each set of words).

Right now the formula that I have (and works) is: =SUMPRODUCT(COUNTIFS(Data!$AA:$AA,"*"&NamedRange1&"*",Data!$AA:$AA,"*"&NamedRange2&"*"))

(Data!$AA:$AA is the column with all the sentences)

My problem with this is that I have to type in the named range manually for every single cell.

My question is how to reference a named range in a formula such that i don't need to type in the named range manually in each cell? I would like to be able to double click (or Ctrl +D) so that each cell from column A and row 1 refers to the named range and counts automatically whether or not and how often the requested words appear in the sentence.


Thank you