# Lookup and show multiple items from list - [SOLVED]

1. ## Lookup and show multiple items from list - [SOLVED]

Good morning all,

I have been working on the problem and have issues getting the right solution.

I have a list with 110000 entries and for argument sake I will use fruit

The list:
Italianapples
Italianapples
Chineseapples
Spanischcoconuts
Dutchbanannas
USapples
Frenchgrapes
Italianapples
Mexicanberries
Belgianapples
and 110000 more

In a different tab I will write the value to search for, including wild cards like ? and *:
*apples

Now I would like the following list to show up in a different tab:
Italianapples
Italianapples
Chineseapples
USapples
Italianapples
Belgianapples

Looking up the first match or checking number of matches works ok. But showing all matches in a list is a problem for me.

All help will be appreciated.

Thanks

Duncan

2. ## Re: Lookup and show multiple items from list

Welcome to the formum Duncan,
Attach a sample workbook. Make sure there is just enough data to demonstrate your need. Include a BEFORE sheet and an AFTER sheet in the workbook if needed to show the process you're trying to complete or automate. Make sure your desired results are shown, mock them up manually if necessary.

Remember to desensitize the data.

Click on GO ADVANCED and then scroll down to Manage Attachments to open the upload window.

3. ## Re: Lookup and show multiple items from list

One way using formulas would be :

=INDEX(\$A\$1:\$A\$11, SMALL(IF(FREQUENCY(IF(ISERROR(SEARCH(TRANSPOSE(\$D\$1), \$A\$1:\$A\$11)), "", ROW(\$A\$1:\$A\$11)-MIN(ROW(\$A\$1:\$A\$11))+1), ROW(\$A\$1:\$A\$11)-MIN(ROW(\$A\$1:\$A\$11))+1)=ROWS(\$D\$1), ROW(\$A\$1:\$A\$11), ""), ROW(A1)))

array entered. Can be wrapped in a IFERROR to manage errors.

Hi NFL

Ok , will do

5. ## Re: Lookup and show multiple items from list

Try

In Sheet2 A2 with data in Sheet1 A2:A100 and search value in G1 of Sheet2

=IFERROR(INDEX(Sheet1!\$A\$2:\$A\$100,SMALL(IF(ISNUMBER(SEARCH(Sheet2!\$G\$1,Sheet1!\$A\$2:\$A\$100)),ROW(Sheet1!\$A\$2:\$A\$100)-ROW(\$A\$2)+1,""),ROWS(\$1:1))),"")

...confirmed by pressing CTRL+SHIFT+ENTER to activate the array, not just ENTER. You will know the array is active when you see curly braces { } appear around your formula. If you do not CTRL+SHIFT+ENTER you will get an error or a clearly incorrect answer.

Then copy down column

6. ## Re: Lookup and show multiple items from list

Used help from John and Paul

Attached a sample xls below. Now where did I go wrong ?

7. ## Re: Lookup and show multiple items from list

G4=IFERROR(INDEX(\$C:\$C,SMALL(INDEX((LEN(\$C\$4:\$C\$10000)=LEN(SUBSTITUTE(UPPER(\$C\$4:\$C\$10000),UPPER(\$E\$4),"")))*10^10+ROW(\$C\$4:\$C\$10000),0),ROWS(\$G\$4:\$G4))),"")
Try this and copy towards down

Note: change *apples as apples

8. ## Re: Lookup and show multiple items from list

Search parameter does NOT require wild card i.e Apples will do.

Second change Formulas==>Calculation Options to "Automatic"

9. ## Re: Lookup and show multiple items from list

Update .... now where did I go wrong .... ? Rev 002 attached.

PS: With over 110000 rows in my 20 column table tha Automatic setting makes everything way too slow. But for this small fruit test it will be a better setting of course

10. ## Re: Lookup and show multiple items from list

if you post your original file by removing sensitive data if any, we can to it with helper column and pivot table
in this case you have to refresh pivot table when you change your criteria (search item)

11. ## Re: Lookup and show multiple items from list

Thanks NFL, not sure why it would be better to post a table with close to 2 000 000 entries and filled with formulas. I think that would make it more difficult and besides that it will be a large file and will bog down many computers , just saving it might take a couple of minute.

Seems to me the fruit example is a good representation.

12. ## Re: Lookup and show multiple items from list

Only now I realized the formula need sto be copied to the other rows. This means one needs to know the number of hits before hand, or brute force copy it a 110000 times. I was hoping there is a formula that does not require this.

Thanks for all the help guys. I think I am getting close to the understanding your tips and suggestions.

But I am still going wrong in the first formula from Paul.

13. ## Re: Lookup and show multiple items from list

If you have 2 formulas that work ...???

Regarding the number of rows being variable: you can create dynamic name ranges which automatically adjust when rows are added / deleted

There is any example in that attached:

Named Range:; Mylist

Refers to: =TheResults!\$B\$4:INDEX(TheResults!\$B\$4:\$B\$200000,COUNTIF(TheResults!\$B\$4:\$B\$200000,"?*"),1)

Look in Formulas==.Name manager

Formulas is:

=IFERROR(INDEX(MyList,SMALL(IF(ISNUMBER(SEARCH(\$C\$4,MyList)),ROW(MyList)-ROW(\$G\$4)+1,""),ROWS(\$1:1))),"")

14. ## Re: Lookup and show multiple items from list

remove the rows no problem, I think it would be better with the original file format (with dummy data), so that will create helper column and create pivot table
later you can add data (more rows)

15. ## Re: Lookup and show multiple items from list

maybe try this one (done with PowerQuery/Get&Transform)

16. ## Re: Lookup and show multiple items from list

Originally Posted by nflsales
re.........I think it would be better with the original file format (with dummy data), so that will create helper column and create pivot table later you can add data (more rows)
Helper column would increase the table with original data by 110000 cells wouldn't it ?

17. ## Re: Lookup and show multiple items from list

Originally Posted by JohnTopley
If you have 2 formulas that work ...???
The formula 2 and 3 behave different with wild cards like ??? and * and I was wondering how Formula 1 would react to wild cards.

18. ## Re: Lookup and show multiple items from list

Originally Posted by JohnTopley
I

Regarding the number of rows being variable: you can create dynamic name ranges which automatically adjust when rows are added / delete
Thanks. Never used the range feature before

But I still need to copy the formula "=IFERROR(INDEX(MyList,SMALL(IF(ISNUMBER(SEARCH(\$C\$4,MyList)),ROW(MyList)-ROW(\$G\$4)+1,""),ROWS(\$1:1))),"")" a 110000 times in order to see the max possible number of results. Correct ? edit some time later --> I think I did someting wrong in the formula name manager. Will do some more trouble shooting tonight.

In order to use a flexible range would it be possible to refer to a table column on another sheet. I tried it but could not get it working.

19. ## Re: Lookup and show multiple items from list

The attached now has the named range on Sheet1.

And you need drag the formula down for the likely maximum number of any criteria so if you never have more then 200 of any given item, then the formula "only" needs to in 200 rows.

20. ## Re: Lookup and show multiple items from list

Thanks John and all other people posting to solve my problem !

My formula is now as follows:

``Please Login or Register  to view this content.``
It works perfect and in variable C10 I can use wildcards like ? and *.

Although my range with data actually starts at E5, not at E4. But when using E5 the results are incorrect.

I would like to understand why this is because I have the feeling the problem lies elsewhere and not in the INDEX function.

21. ## Re: Lookup and show multiple items from list

TRy

=IFERROR(INDEX(DATASHEET!\$E\$5:\$E\$107662,SMALL(IF(ISNUMBER(SEARCH(\$C\$10,DATASHEET!\$E\$5:\$E\$107662)),ROW(DATASHEET!\$E\$5:\$E\$107662)-ROW(\$E\$5)+1,""),ROWS(\$E\$5:\$E5))),"")

22. ## Re: Lookup and show multiple items from list

Dang, now why did I miss that. Thanks. Now it makes sense. Thanks a lot.

One more question. When using INDEX it seems impossible (at least I can not get it working) to refer to a table column in another sheet.

INDEX(DATASHEET!\$E\$5:\$E\$107662 ..........................

Something like:
INDEX(NAMEOFTABLE[@NAMEOFCOLUMN] ..........................

23. ## Re: Lookup and show multiple items from list

I confess not being familiar with nor using tables: I am (probably through ignorance!) not a big fan as I find referencing data in formulae using tables long-winded and not very comprehensible. For example dragging table-based formula compared to range-based formula is (or appears to be) a pain.

But to answer your question: you can reference a table in any sheet.

It would help (as normal) to post a workbook.

Attach a sample workbook (not image).so that we do not have to manually key in your data to do a testing.

Make sure there is just enough data to demonstrate your need. Include a BEFORE sheet and an AFTER sheet in the workbook if needed to show the process you're trying to complete or automate.

Make sure your desired results are shown, mock them up manually if necessary.

Remember to desensitize the data.

Click on GO ADVANCED and then scroll down to Manage Attachments to open the upload window.

24. ## Re: Lookup and show multiple items from list

Originally Posted by JohnTopley
.......For example dragging table-based formula compared to range-based formula is (or appears to be) a pain.
Sometimes I do not have to drag (some how the formula is copied to all rows automatically) and when I do have to drag it works as dragging always works. Of course with 100000+ rows one can not drag (takes too much time) but has to copy & paste the formula.

But to answer your question: you can reference a table in any sheet.
That made me go back and double check everything. Took me about 45 minutes and got it working.

Think the weird 2x(ctrl+enter) did the trick.

Thank for all the help John

25. ## Re: Lookup and show multiple items from list

Tables automatically expand when you add rows (one of the "claims to fame" of tables but no better in my view than using dynamic ranges).

Note: you not need "wildcards" with SEARCH: in your posted example you will get the same result with "App" as the search criteria

26. ## Re: Lookup and show multiple items from list

Originally Posted by JohnTopley
.............
Note: you not need "wildcards" with SEARCH: in your posted example you will get the same result with "App" as the search criteria..........
For my xls wildcards like ? and * are of utmost importance.

In the example using "app" will result in 7 hits, using "???????app" will result in 6 hits. The last one ignores USapples, as it should. Perfect example the wildcards work as I need them.

There are currently 1 users browsing this thread. (0 members and 1 guests)

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1