• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Return multiple items w/ one lookup value: return ALL instances of text string portion?

P

Peacedout

Member
Let's start with the example and formula found in the "Return multiple items with one lookup value" video.

Let's say that in Column A, in addition to ALICE, there's also an ALAN and an ALDO. How do you amend the formula to return all the cells that contain "AL?"
 
I read that section yesterday...
And clearly, I'm such a rank beginner, I don't understand what portion of Luke's explanation pertains to my question. I feel like I need my lookup value to be "AL*" but that doesn't seem to work.




.[/quote]
 
Hi ,

One way is to use the SEARCH function , but this will return the name if the characters AL occur together anywhere in the name , not just the beginning , as in Caleb.

=INDEX(Names,SMALL(IF(ISNUMBER(SEARCH("AL",Names)),ROW(Names)-MIN(ROW(Names))+ 1),ROW(A1)))

Enter this as an array formula , using CTRL SHIFT ENTER.

Narayan
 
I think :confused: that finding the text string anywhere it occurs in the cell would probably suffice, because in my ACTUAL data, as opposed to the data in the video I used as an example, I'm just looking for a way to "narrow down" potential matches from thousands of options (if that makes sense). Now I'm excited to "mess around" with it tomorrow morning and see if it works the way I hope it will.

Thanks very much.
 
Deepak, that just returns the number of cells that contains that text string, not the cell contents that contain that text string, though, right? Thanks anyway!
 
NARAYANK991 said:
Hi ,

One way is to use the SEARCH function , but this will return the name if the characters AL occur together anywhere in the name , not just the beginning , as in Caleb.

=INDEX(Names,SMALL(IF(ISNUMBER(SEARCH("AL",Names)),ROW(Names)-MIN(ROW(Names))+ 1),ROW(A1)))

Enter this as an array formula , using CTRL SHIFT ENTER.

Narayan
Click to expand...

Even with using it as an Array formula, it only returns the first value (e.g. Alice) that contains the text string, and not the remaining two. I must not be doing this correctly.
 
Hi ,

The formula will only return one name in one cell ; copying it downwards will return the subsequent names from the list. Thus , if there are 3 names which meet the criterion , you need to put this formula in 3 cells one below the other.

Narayan
 
Ah-ha! Not actually being able to "think my way through" your formula, I copied the array formula across multiple columns (as that was how it was presented in the example video), instead of down multiple rows. Now it returns the values correctly. Thank you.

To be completely honest, I'm not sure I can combine these two formulas to do precisely what I need to do in my "real" work. I think I read we're supposed to upload samples in the most recent Excel, but I'm using 2007. Is it okay to do it, anyway?
 
Essentially, I'm trying to write a formula in a spreadsheet that does what the "FIND ALL" command in a spreadsheet does, but without opening the spreadsheet that contains the data.
I want the formula to return the IDs for all possible matches after searching with a) the name or part of the name b) the alternate name or a part of the alternate name or c) the address or part of the address.
As you can see, many cells may contain the same text string (e.g. Brookside Apartments and The Brook Commercial Building) or the same numerals (e.g. 20280), so I would like all matches listed.

I really appreciate the help!
 

Attachments

NARAYANK991 said:
Hi ,

See if this is OK.

Narayan
Click to expand...

Narayan, one more extremely pertinent question that is making me hold my breath. In the example I uploaded, the source data and the formulas are on different worksheets in the same workbook. In the real world, my formulas will be in a different workbook than the data. Does the source workbook have to be open for the formulas to work? Because that's what it seems like....and I'm already starting to cry just a little bit....:(
 
NARAYANK991 said:
Hi ,

You are right ; the workbook which has all the data has to be open for the formulas to show the matches.

Narayan
Click to expand...

AAAAAAAAaaaaaaaaahhhhhhhhhhh. Well, that pretty much defeats the purpose of what I'm doing. However, I really appreciate all your help. I likely could have worked on that until the day I die and I wouldn't have figured it out!
 
Hi ,

One alternative is that you use VBA , which can automatically open the data workbook , retrieve the matches , and then close the data workbook , without the user having to do anything.

Narayan
 
You must log in or register to reply here.
Back
Top