So the title of the thread says it all:
vlooking shows only the first match. But should there be more than one matching value, how to tell excell to keep looking thu the whole matrix until a match before reporting the same as the first result?
ie
1112 a
1112 e
Vlookup, seems to always give "a" as result.
How do I tell excel to keep looking until it finds "e"?
Thanks again!
Vlookup and Index-match formulas will only ever return the first instance they comes across. They are "dumb" functions that will stop once it's search criteria is met. This is why you need to have unique reference values.
Don't just use the answers provided for you. Try to understand how it works by reverse engineering or asking about it.
Please mark the thread as [SOLVED] (Thread Tools->Mark thread as Solved) when answered.
If you're happy with an answer given, please click the * under the person's name to boost their reputation.
array formula index/small/if -ctrl+shift+enter NOT just enter
1112.xlsx -excel 2007/above
1112xl2003.xls - lower version
Note: Thanks Marvin
Last edited by vlady; 06-07-2013 at 09:56 PM.
I think people forget the word "THANK YOU!!!!" Do you still know it???
There is a little star ( ADD REPUTATION ) below those person who helped you. Click it to say your "PRIVATE APPRECIATION TO THEIR EFFORT ON THEIR CONTRIBUTIONS "
Regards,
Vladimir
Hey Vlad,
You should have saved your file as a .xls instead of a .xlsx as the OP is using 2003 Excel. Make sense?
My guess is they won't be able to see your work because of the older version of Excel.
One test is worth a thousand opinions.
Click the * Add Reputation below to say thanks.
my bad, i didn't notice that, thanks.
I've clearly been put in my place and shown that it's somewhat possible. I don't see the utility in this however. May as well save the trouble of creating this formula and just use filters/sorts. That's just my opinion though.
Craig, dont think of it that way, rather that you have learned a new technique - with excel that happens to every-one, all the time
1. Use code tags for VBA. [code] Your Code [/code] (or use the # button)
2. If your question is resolved, mark it SOLVED using the thread tools
3. Click on the star if you think someone helped you
Regards
Ford
Hey Craig,
I agree with you on using filters and sorts (most of the time). When I try to create long SumProduct or CSE formulas it makes me dizzy. I believe a simple filter and/or sort will do a lot of these problems that are asked....
That being said.. I had to learn what these smart guys were doing with SumProduct and Array Formulas so I wouldn't feel stupid. I'm a lot better with them now but still prefer Sort and Filter. I really like Pivot Tables instead of SumProducts....
Re: vlooking shows only the first match. How to keep on looking?
Hi Mega_gerry,
I have solution for you.
=IFERROR(INDEX($L:$L,SMALL(IF($K$2:$K$60478=$A2,ROW($K$2:$K$60478)),COLUMNS($B2:B2))),"")
for example see the attached file.
if found my solution usefull to you then
CLICK the STAR * icon on the left side below my user name to say thank you.
@ FD & Marvin - Please don't get me wrong. I like to reverse engineer the solutions provided, by those better versed in excel than I am, in hopes of remembering how/when to use it. I don't want to hijack this thread, so I'll just say I definitely learned from the solution Vlady gave.
Cheers
You may want the extracted data to appear on a different sheet or in a different file.Originally Posted by Craig K.
You might want this to be dynamic based on other user inputs or other formulas.
You might want to use this data as the selections in a drop down list.
This type of question is asked frequently.
Biff
Microsoft MVP Excel
Keep It Simple Stupid
Let's Go Pens. We Want The Cup.
Thanks all for your help!
I have quite a few interesting leads here.
I am still working on this.
Not sure I have fully debugged it.
I need to be able to find the next match, but keep it aligned with the original row, instead of just a list of options.
Any thoughts?
Thanks again
Are you trying to find if a specific value shows up for each number? Example: did "e" occur 1112?
Not really,
I have for instance: (numbers in column A, letters in cloumn B)
1495 E
1021 F
1495 S
1145 R
1495 T
The vlookup finds the 1st 1495 and reports E
but does not report S OR T since it always stops at the fisrt 1495.
What do you want your output to look like then?
should look like this. see attached.
I am simply comparing 2 sets of data, and need to see if they match or not.
thanks
It looks like you want a simple side-by-side comparison and for any differences to be highlighted.
more or less. It's not that simple.
some numbers might not exist in one list but yes in the other.
and the numbers that are repeated still need to find a match. That's the mayor problem I have.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks