I need to search for 256 different cells in a document Is there anyway I can use a Vlookup formula or a macro to find all of them at once and recieve corresponding data from cells within the same row ? attached is an example of what im looking for.
thank you
Last edited by jlamannaphoto; 10-07-2011 at 09:01 AM.
See what you think of the attached. Note the vLookup returns a "0" if result is blank cell, this can be overcome if it's an issue.
Dave H
that looks great, . the formula works great I just had to shift over the range and its working perfectly
Thank you so much!
this formula is pulling in extremely strange data from i dont know where.. attached is the file im working on, please help
thanks again for your time,
Jeff
Something like =VLOOKUP(P5,D3:L3436,2,TRUE) pulled down will effectively give strange results
because the lookup range is relative
Maybe use =VLOOKUP(P5,$D$3:$L$3436,2,TRUE)
If you are looking for exact matches, then the last argument should be FALSE
=VLOOKUP(P5,$D$3:$L$3436,2,FALSE)
ChemistB
My 2?
substitute commas with semi-colons if your region settings requires
Don't forget to mark threads as "Solved" (Edit First post>Advanced>Change Prefix)
If I helped, Don't forget to add to my reputation (click on the little star at bottom of this post)
Forum Rules: How to use code tags, mark a thread solved, and keep yourself out of trouble
I tried and ... if you do a search of the document for 80 neoprene you will find that its a 3m chemical located in two different areas and this doesnt match the Vlookup results at all, i tried entering the formula and showed the results on this new attachment with comments about what im looking for.
thanks
The TRUE argument will only work if your table is sorted in alphabetical order AND, if it doesn't find an exact match, it will return the next closest thing that comes alphabetically before it.
okay that sounds very reasonable lemme give it a go and see where we end up
wow that worked out really well, i only have one more question, can it pull multiple results? for example I am search for chemicals in the building and they are in more then one area, can it pull multiple results? maybe with an if argument added to the vlookup formula?
thank you so much you saved me alot of time and frustration!
You should heed the above comments about using an absolute table range ie $D$3:$L$3436 - if you check your "help" sheet you will notice the table range is significantly different at the bottom of the table! Also using the TRUE argument returns the CLOSEST match if you change this to FALSE the majority of your search terms do not exist in the source list.
Beware of inaccurate results!!
Dave H
Dave,
thanks I have changed it ... here is the updated file,
on a side note i was talking to someone about setting the data up as an array to pull multiple values for items found? any ideas on how one would go about doing this this?
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks