I am very new to Excel and fomulas, so please be gentle.
I found an article that is exactly what I am looking for.
https://www.myonlinetraininghub.com/excel-search-string-for-a-list-of-words
I have data in Columns A and B. The list is around 3500 row. I have a list in column H--named "list" and it contains 1400 items. I want to match the "list" to words found in B, and place that in column C.
So here is an example:
line of A and B:
| REC 196 DATA 80 CHAR OPER '5636 JOB J217386W' | EXEC DOIT PARM='JOB=J217386W ' |
In Column H--the list column--the word J217386W is present
The formula I am using is:
=@INDEX(list,SUMPRODUCT(ISNUMBER(SEARCH(list,B3))*ROW($1:$1500)))
But all that returns in the #N/A with an error: A value is not available to the formula or function.
What am I missing?
Hi Patrick,
The list should have an exact match to the data in column B. In your example, the list in column C should only contain J217386W as the rest of the text is not present in column B.
Can you remove everything other than the Job numbers from the list?
Mynda
I've attached my file. Perhaps you could give it a look and give some counselling and direction. I really want to understand this without having to drop it in a database and running a query loop.
Thanks
Hi Patrick,
This formula is handling arrays; list and ROW. You must ensure the arrays are the same size i.e. the list name should reference cells $H$1:$H$1416, not the whole column. And the ROW formula should reference rows $1:$1416.
You should also handle errors by adding an IF statement because not all job numbers are present in list:
=INDEX(list,IF(SUMPRODUCT(ISNUMBER(SEARCH(list,B2)))=0,NA(),SUMPRODUCT(ISNUMBER(SEARCH(list,B2)))*ROW(1:1416)))
Mynda
Should there be $ on the 1:1416 to lock the rows?
=INDEX(list,IF(SUMPRODUCT(ISNUMBER(SEARCH(list,B2)))=0,NA(),SUMPRODUCT(ISNUMBER(SEARCH(list,B2)))*ROW($1:$1416)))
When I copy this down the rows in the without it, the numbers increase.
I changed to the above, and I still receive an #N/A in a cell I know to be a match, but just an #N/A with no error attached.
I really am trying to understand this.
The first part: (list,IF(SUMPRODUCT(ISNUMBER(SEARCH(list,B2)))=0,NA()
say if no match found, mark with NA
The second part: SUMPRODUCT(ISNUMBER(SEARCH(list,B2)))*ROW($1:$1416)))
is the doing the actually find and set to the Name if found.
I understand what it is supposed to be doing, I just can't seem to get it to work.
I appreciate the time taken to educate a novice.
Hi Patrick,
Yes, ROW reference should be absolute. In the file you attached the formula in cell C4 is looking up B2. I also noticed that there are duplicates in the list in column H which need to be removed to avoid #REF! errors. When you remove the duplicates, you have 475 records left.
You also haven't changed the range referenced by the named range; list, it's still referencing the whole of column H. It should be $H$1:$H$475 after removing the duplicate records.
I gave you the wrong formula earlier as it was missing the double unary (--) characters. Here is the correct one:
=INDEX(list,IF(SUMPRODUCT(--ISNUMBER(SEARCH(list,B4)))=0,NA(),SUMPRODUCT((--ISNUMBER(SEARCH(list,B4)))*ROW($1:$475)))) Mynda
Mynda,
Thank you ever so much. I didn't understand the range thing on the list--it wasn't sinking in, but I do now. I also didn't know Duplicates mattered, but I now see they will.
I appreciate the assistance and the patience. You rock!
