We all know that Offsetmatch can fetch an array of cells based on the lookup value. But what if i have the same value is more than once in the lookup column? Can i fetch as many array as i have the value in the column? Earnestly looking for the answer.
Okay.. would you like to share a sample workbook Else I would be happy to create a sample solution for you, if you can incorporate that to your work ? Thanks.
Regards,
DILIPandey
<click on below * if this helps>
DILIPandey, Excel rMVP
+919810929744 (India), +971528225509 (Dubai), [email protected]
umm we do?
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
In my attached worksheet I have 5 columns (First Name, Surname, Age, Salary and Marital Status)and 7 rows excepting the title row. In the First Name column there are three Paul with different Surnames. I'd like to look up the First Name column by "Paul" and offset the last three columns (Age, Salary and Marital Status). Since there is 3 instance of "Paul" in the first column, can i offset three arrays by a single array formula? Or should i need three formula?
Dear Dilip
Please see the attachment in my above reply. Please help me out.
Put this ARRAY formula anywhere you like(let's say in H2). Copy down and across as you like.
=IFERROR(INDEX(A$2:A$100,SMALL(IF($A$2:$A$100="Paul",ROW(A$2:A$100)-1),ROW(A1))),"")
Regards
Fotis.
-This is my Greek whisper to Europe.
--Remember, saying thanks only takes a second or two. Click the little star * below, to give some Rep if you think an answer deserves it.
Advanced Excel Techniques: http://excelxor.com/
--KISS(Keep it simple Stupid)
--Bring them back.
---See about Acropolis of Athens.
--Visit Greece.
Thanks Fotis! It worked. But is it possible to put a cell reference instead of putting "Paul" in the formula so that each time i put a different name in that cell it shows different array as a result. Or can i have the lookup value from a calculated result in another column? Will it become volatile then? I mean reduce the performance of excel?
You can use a cell reference.
As this formula is ARRAY, then if you have many data, maybe will be slow....I don't think that you'll have any other problem..
Dear Fotis,
I tried putting a cell reference in the formula in place of "Paul", but it returns 0 in the result array.
I have put the formula in h2 and copied down and across with cell reference G2 instead of "Paul". Now with data validation (List) G2 shows the values of column A (First Names) and the result changes according to what i select from the in-cell drop-down.
Now is it possible to make G2 show the unique values only. Check the attachment please.
See attached:
Column N has formula to create unique list from A: named range "Unique" entered with Ctrl+Shift+Enter
The data validation in G2 refers to "Unique"
Nice mate. I guess the array formula has to be scrolled down untill i reach to Zero. Cheers!
Sorry John,
again marking it 'Unsolved' as i have got some questions.
Could you explain the mechanic of the nested Countif function in column 'N'? How is it evaluating an array ($A$2:$A$20) against another array ($N$1:N1) and what is it counting? It should be returning numbers, then how come the Match function getting its lookuparray from the counif result? And how the zero at the N8 cell?
I'm baffled.
Last edited by bubai; 07-13-2015 at 12:18 AM.
It is matching the entries in the N array against those in the A array. If the MATCH is 0 i.e. COUNTIF is 0, then name isn't in N so it adds it to N. An Array formula checks each cell in the range (a2, A3 ,A4 etc )
"Google" array formulas and you will find (many) explanations of how they work.
Yeah, I understand how it worked.
But why is the 0 in only one cell (N7) but not afrerwards even if the formula is scrolled down?
Last edited by bubai; 07-14-2015 at 12:18 AM.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks