Looking for formula to extract values in column A (Distinct Zippa heading) into column D. Column B data is the primary data to be used to look into column A and if there is a match of the data in column B in column A, the formula should extract the match in column A into column D. See sample data.
Thanks
What do you mean by non-array formula? It's impossible to do this without using arrays.
Ali
Enthusiastic self-taught user of MS Excel who's always learning!
Don't forget to say "thank you" in your thread to anyone who has offered you help. It's a universal courtesy.
You can reward them by clicking on * Add Reputation below their user name on the left, if you wish.
NB: as a Moderator, I never accept friendship requests.
Forum Rules (updated August 2023): please read them here.
AliGW: non-array meaning without using Control + ALT + Delete. Here is an example of non-array:Thanks
Please Login or Register to view this content.
Last edited by bjnockle; 01-18-2020 at 02:31 PM.
OK. Do you have the Power Query add-in?
AliGW: No, I do not have Power Query add-in
That's a shame. I think you are going to struggle because of the lookup array to determine which numbers to include. Hopefully someone can do something with the AGGREGATE function for you.
Just out of interest, why do you not want formula with CSE?
I hope it was jokeControl + ALT + Delete
The array function isFormula:
Please Login or Register to view this content.
Also you can use MSQuery. This tool is always present. Put example in to c:\temp or change path and name in the connection.
Last edited by BMV; 01-18-2020 at 03:13 PM.
How about
=IFERROR(INDEX($A$2:$A$63,AGGREGATE(15,6,(ROW($A$2:$A$63)-ROW($A$2)+1)/(ISNUMBER(MATCH($A$2:$A$63,$B$2:$B$21,0))),ROWS(D$2:D2))),"")
Aggregate is not available for 2010.
Yes it is, that's when it was first introduced.
We should all be demanding access to the latest Excel, because the FILTER function makes quick work of this.
=FILTER(A$2:A$63,COUNTIF(B$2:B$21,A$2:A$63))
However, it's a spilled function, so arguably an array formula.
My own contribution,
D2: =INDEX(A$2:A$63,SMALL(INDEX((1-SIGN(COUNTIF(B$2:B$21,A$2:A$63)))*1000+ROW(A$2:A$63)-1,0),ROWS(D$2:D2)))
which works without array formula entry in Excel 2000, so presumably also in all later versions. It returns #REF! errors when the data has been exhausted.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks