• 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.

Finding a value in range of cells, returning potential values

2

2L8IWON

New Member
Hi all -


I've found a number of similar queries, but nothing that does exactly what I think I need (unless I just don't know the terms, which is possible). Anyway, I have a list of fixed assets and I need a user to enter a term and see if that asset already exists. I.e. let's say I want to buy a Dell E5510 laptop. I want to be able to type in E5510, hit a 'search' button, and return the location of any E5510 laptops currently in inventory (which I have in a separate list).


Two thoughts:

I would use VLOOKUP, but I want the 'search' function to bring back all results - maybe I have 2 E5510s in St. Louis, MO and 3 in Santa Ana.


The E5510 may be the search term, but in the fixed asset list, it may be called 'Dell E5510 laptop 2xRAM' - i.e. it won't be cleanly labeled E5510.


Any way you guys see that I can do this?


Thanks!


Mike
 
In a separate thread, Hui posted a response to a similar question regarding arrays that basically says search for anything in the first column with the specified letters (in this case 'E5510') and bring back the sum of those items. Another posted indicated you can use SEARCH to avoid the case sensitivity of FIND.


=SUMPRODUCT(1*(IFERROR(FIND(C1,A1:A9),0)>0),(B1:B9))


So, this formula would allow me to count the instances of the E5510's in inventory. But, now I want to bring back where they are. Thoughts on the easiest way to combine these particular queries?


Thanks!
 
You could use the technique I described here:

http://chandoo.org/wp/2011/11/18/formula-forensics-003/


to pull the results. The trick is writing the criteria to show which cells to return. Hui put you on the right track, formula would be something like:

=INDEX(ReturnColumn,SMALL(IF(ISNUMBER(SEARCH(FindThis,SearchColumn)),ROW(SearchColumn)),ROW(A1))


Note that I used the SEARCH function instead of FIND. FIND is case-sensitive, while SEARCH is not.
 
Good evening 2L8IWON

you may find the attached workbook of help.

There are two methods of returning data, a single search return and a multi return.


https://dl.dropbox.com/u/75495784/Correct_Formula-IfError_with_Vlookup.xlsx


the multi is a formula that I received from Luke M
 
Bob - I like the example, thanks much! I guess my issue with yours would be that the VLOOKUP has to find the exact name of the sales person (i.e. Flores, Tia). But what if you wanted it to find any transaction where a sales person whose name contained Tia as involved (in case, for example, multiple sales people were involved in an order). Just trying to get back to my issue wherein I have E5510 laptops, for instance, combined with other naming nomenclature in the same description.
 
2L8IWON

The multi lookup does return all the results related to one person for instance if you look at Flores, Tia in the data columns and turn a south to a north it is updated in the vlookup section these months could be items of any thing
 
Bob - got it. However, what if instead of having a drop down, I have a free text field where the user types in 'Tia', but does not type in 'Flores, Tia'. Then, the lookup doesn't work, correct? That's the issue I'm having. Individuals will not always type in the same uniform name, nor will the assets be listed cleanly as 'Flores, Tia' each time. Does that make sense? or am I not understanding what you're saying.


Luke - thanks! I am playing around with your formula as well. I have the same issue as with Bob's - if I wanted to type in 'Veg' instead of 'Vegetable', I won't get any results. How can the formula be modified to pull this back?


Thanks guys!
 
2L8IWON

I understand what you are after but to be honest the one thing I have learned about people is they NEVER enter a data choice the way they should, if you want them to enter carrot they will input cabbage, there is not much you can do about stopping them (some strange laws about not being allowed to rip their throats out) that's why Drop downs limits the choice to what you want, and you can issue warnings of impending doom if they try to enter some thing of their own choice.

If you are not going to have consistent data names I think you may have a problem in your searching.

You may be able to have a formula that has wildcards but even then if the names, or goods records do not follow some sort order even this would fail, you must have some sort of data input structure.

I would have more than one drop down, one for searching your data and drop downs for data input so that those entering data choose the product from a list
 
Bob - thanks for your help, again. The drop-down you suggested won't work due to the vast scope of the possible inputs. So, I can't go that route (i'd have to put hundreds of options into the drop down list).


A formula that I found that gets me part of the way there is:

=INDEX($A$1:$B$7,SMALL(IF($A$1:$A$7=$A$9,ROW($A$1:$A$7)),ROW(1:1)),2)


Where it references cell $A$9 I want to substitute a wildcard type of input like “*”&A$9&”*” so that if someone inputs 'DELL' in A9, it'll pull back anything that includes DELL on separate lines.


Anyone have thoughts on how to make that work?


Thanks!

Mike
 
You may have missed my earlier formula with SEARCH


=INDEX($A$1:$B$7,SMALL(IF(ISNUMBER(SEARCH($A$9,$A$1:$A$7)),ROW($A$1:$A$7)),ROW(1:1)),2)


Also, for the other formula with VLOOKUP, all of the lookup functions support wildcards. So you could do something like:

=VLOOKUP("*"&$A$9&"*",....)
 
For anyone wondering, it looks like this approach will do the trick:

http://forums.techguy.org/business-applications/731243-solved-excel-how-use-wildcards.html


For instance, if you download the attachment, you can enter B, Bo, or Bob and it will fetch any results that include each of the three entries. I'll tweak this to my purposes, hope it helps someone else!


Mike
 
Assuming that you enter your partial data i.e. "Veg" in cell A1 and want to see if this is available in Range C1:C1000 (inventory list which has word "Vegetable") then try:

=LOOKUP(9.99E+307,SEARCH(A1,C1:C1000,1),C1:C1000)
 
he wrote the text always able to make people resonate.trailmeme.ioqz.Related articles: http://rtidev4.,abercrombie outlet
when he can become sure? to see their own position. michael kors handbags,jordans shoes, If I persist,louboutin pas cher, Bono blazing eyes,jordan,jordans shoes,sac lancel,E8.8C. for example,louboutin, Even the pigs living in the villa.
Because you are good to me.Abercrombie Outlet,http://www.drbrainhacker.org/spwiki/index.php/User:Ezgetsenluh#orgwikiUserHpwjio,abercrombie france,http://cannabisseur.org/User:79200348973#I_am_happy_to_say_I,A4. If I persist,air jordan, ralph lauren,abercrombie, will be carefully collected and treasured in my heart.Related articles: http://www. mtu. 2001 -- 2006 I became the four session of the Luohe Municipal Committee of the CPPCC Standing committee in where? Abercrombie.
Essay Network Yzv China touches the recent Beijing University student suicides occur frequently,http://local-gay.co.uk/lg/pg/blog/read/84586/comindex-stay-left,sac lancel pas cher,it suddenly happenednet/bbs/viewthread.just a feeling I am back to God. forever and ever far so free and easy. Abercrombie. Related articles: http://blog.
 
You must log in or register to reply here.
Back
Top