HOW to mass look up a text in a string of numbers?

September 14, 2018 at 18:58:31
Specs: Macintosh
i have a question for excel lookup functions. will you beable to help me?

I have like specific codes which is in a column of cells and i like to find these codes in a table. v lookup funciton only works if the 2 cells match. what if the two cells do not exactly match but the code eg "SC123456" which i want to find is in like a string of text eg "chocolate sc123456 flavor". how can i mass find these kind of info? now i have to manually copy the code then control F to find where it is in a spreadsheet. is it possible to do it as a mass formula so i do not have to do it one by one? i would like the formula to like find the cell that contains my code, then return me the whole row.



See More: HOW to mass look up a text in a string of numbers?

Reply ↓  Report •

#1
September 15, 2018 at 07:00:35
I'm not really sure what you mean by "mass find".

re: "SC123456" which i want to find is in like a string of text eg "chocolate sc123456 flavor"

If you are trying to locate the address of the cell that contains your partial string, that can be done as follows:

With this data:


        A                    B                       C
1   SC123456     chocolate sc3333333 flavor        XXXXX
2                vanilla sc4444444 flavor          YYYYY
3                orange sc123456 flavor            QQQQQ
4                lime sc2222222 flavor             DDDDD

This formula will return $B$3

=ADDRESS(MATCH("*"&A1&"*",B:B,0),2)

See here for an explanation:

http://alanmurray.blogspot.com/2014...

re: i would like the formula to like find the cell that contains my code, then return me the whole row.

A single formula can not return an entire row. A formula can only return a single value into the cell that contains the formula.

You could use multiple formulas in multiple cells to return multiple values. For example, this formula will return the value in C3 by determining the location of your partial string and then using that address along with OFFSET to return the value from the next Column:

=OFFSET(INDIRECT(ADDRESS(MATCH("*"&A1&"*",B:B,0),2)),0,1)

Increment the "1" and you'll increment the Column offset.

A macro could used instead, but we would need more information in order to offer that solution. Where is the search string, where is the column of data that is to be searched, where do you want to copied row to be placed, etc.

If you are going to post example data, please click on the following How To link and read the instructions found there.

How To Post Data or Code ---> Click Here Before Posting Data or VBA Code


Reply ↓  Report •

#2
September 16, 2018 at 05:05:29
Upgrade:

This version is a bit more versatile:

=OFFSET(INDIRECT(ADDRESS(MATCH("*"&$A$1&"*",$B:$B,0),2)),0,COLUMN())

By letting the COLUMN function determine the cols argument for the OFFSET function, you can drag the formula along a row and not have to manually increment the column offset. Most likely, the formula will need to be modified to fit your exact situation, so let us know if you need any further assistance.

How To Post Data or Code ---> Click Here Before Posting Data or VBA Code


Reply ↓  Report •

#3
September 16, 2018 at 12:05:34
DerbyDad, please let me know if this is completely un-helpful.

I've never done any programming for Excel. Or any other spreadsheet.
Zero. So I know I don't know what I'm talking about. But...

I'd guess that "mass find" means to find all instances of the search string
and return the entire row that each of them is found in, all at once.

But I wonder if it would satisfy ngedyl to just return a list of row
numbers rather than the contents of those rows.

Also, I wonder if the search is always in one particular column, so you
don't need to search every cell, just the cells in that column.

That's probably perfectly obvious, but I don't seem to be able to keep
myself from trying to be helpful. Especially when there are other things
I really need to be doing.

-- Jeff, in Minneapolis

message edited by Jeff Root


Reply ↓  Report •
Related Solutions


Ask Question