Alphabetize Index/Match results?

[rcmii]

I have a range of data (over 1000 rows -- that could be hundreds of thousand in the future -- and approx 135 columns). Is it possible to write the Index/Match but have the results show up in alphabetic order (or numeric if have cells searching for numeric values)?

I have what I need insofar as the Index/Match is concerned, but it returns the data in the order it's listed in a data dump file. I could always just sort that every time I refresh the data, but I'm wondering if there is a formula work-around that will do this. I am not yet very good at VBA so looking for a formula solution, if one exists.

If this is possible, I am happy to post a sample file if that helps. I hate to spin anyone's wheels so inquiring first on the possibility and how to write the formula and then I can send a file if I can't get it to work and then see if anyone has a more specific solution.

Maybe this is too vague right now...but starting here to see what advice anyone can provide.

(I'm not beholden to Index/Match if there is another solution for retrieving data and alphabetizing it upon the results tab).

Thank you in advance.
RCM

 

[Eric W]

Please update your profile with the version of Excel you're using. The best solution often depends on which functions you have available. For example if you have SORT and XLOOKUP, you could do this:

Book1
	A	B	C	D	E	F	G
1	List	Sorted list				Animal	Number
2	15	cat				dog	1
3	2	dog				cat	2
4	6	dolphin				pony	3
5	99	elephant				elephant	4
6	1	lizard				toad	5
7	9	not found				dolphin	6
8	3	peacock				whale	7
9	4	peacock				lizard	8
10	5	pony				peacock	9
11	10	swan				swan	10
12	8	toad				albatross	11
13	9	zorilla				hamster	12
14						iguana	13
15						crocodile	14
16						zorilla	15
Sheet5

Cell Formulas
Range	Formula
B2:B13	B2	=SORT(XLOOKUP(A2:A13,G2:G16,F2:F16,"not found",0))
Dynamic array formulas.

If you don't have those, it's a much tougher problem. Even if you do, then even these functions will probably bog down if you get to hundreds of thousands of rows. So you might be better off with VBA in either case.

 

[rcmii]

Thanks, I will try that as I have SORT and XLOOKUP.

My work version is MS 365 Apps for Enterprise, Excel version 2108

 

[Fluff]

My work version is MS 365 Apps for Enterprise

I suggest that you update your Account details (or click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)

 

[rcmii]

I suggest that you update your Account details (or click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)

Thank you. I will do that.

 

[Peter_SSs]

I have a range of data (over 1000 rows -- that could be hundreds of thousand in the future -- and approx 135 columns)

I'm not quite sure how, if at all, the 135 columns relates to the problem but in case you do get to the hundreds of thousands of rows and if the formula suggestion is too slow then you could try a macro as outlined below. I did notice that you said this was not preferred, but that may change if the formula calculations become too "heavy".

For the layout in Eric's post above your could try this macro. To implement ..
1. With the relevant worksheet active press Alt+F11 to bring up the vba window.
2. In the Visual Basic window use the menu to Insert|Module
3. Copy and Paste the code below into the main right hand pane that opens at step 2.
4. Close the Visual Basic window.
5. Press Alt+F8 to bring up the Macro dialog
6. Select the macro & click ‘Run’
7. Your workbook will need to be saved as a macro-enabled workbook (*.xlsm)

Sub Sorted_List()
  Dim d As Object, AL As Object
  Dim a As Variant, b As Variant
  Dim i As Long
  
  Set d = CreateObject("Scripting.Dictionary")
  Set AL = CreateObject("System.Collections.ArrayList")
  a = Range("F2", Range("G" & Rows.Count).End(xlUp)).Value
  For i = 1 To UBound(a)
    d(a(i, 2)) = a(i, 1)
  Next i
  a = Range("A2", Range("A" & Rows.Count).End(xlUp)).Value
  For i = 1 To UBound(a)
    If d.exists(a(i, 1)) Then
      AL.Add d(a(i, 1))
    Else
      AL.Add "not found"
    End If
  Next i
  AL.Sort
  b = AL.ToArray
  ReDim a(1 To UBound(b) + 1, 1 To 1)
  For i = 1 To UBound(a)
    a(i, 1) = b(i - 1)
  Next i
  Range("B2").Resize(AL.Count).Value = a
End Sub

If you are still needing help (formula or macro - or power query would be another option) then a small set of dummy sample data (say 10-15 rows and 4 columns) and the corresponding expected results (which I gather will actually go on a different sheet?) with XL2BB would be very helpful.