I feel like an idiot for asking this question, as I know I've seen the elements of the solution before. I just cannot remember what they are or where I saw them. Essentially, I want to take a list of indices (row 2, below), sort them numerically, then convert them to values from a lookup table. Using only functions available to all/most versions of Excel. Here's an example: Original Data: D A C convert with MATCH(B1, TableRow2, 0) Data: 4 1 3 Hidden Intermediate result: 1, 3, 4 Desired Final result: A, C, D Lookup Table 1 2 3 4 5 A B C D E The approach I vaguely remember creates an array of TRUE/FALSE values the length of the lookup table (5), then somehow combines that with the second row of the table. (Note that the table would normally be vertical, but it's easier to enter here horizontally.) Thanks in advance.

If you would like to post, please check out the MrExcel Message Board FAQ and register here. If you forgot your password, you can reset your password.

Resorting data to match a list using formulas

Thread starter StarliteLemming
Start date Jun 18, 2022
Tags

sort formula

StarliteLemming

New Member

Jun 18, 2022

I feel like an idiot for asking this question, as I know I've seen the elements of the solution before. I just cannot remember what they are or where I saw them.

Essentially, I want to take a list of indices (row 2, below), sort them numerically, then convert them to values from a lookup table. Using only functions available to all/most versions of Excel.

Here's an example:

Original Data:	D	A	C	convert with MATCH(B1, TableRow2, 0)
Data:	4	1	3
Hidden Intermediate result:	1, 3, 4
Desired Final result:	A, C, D

Lookup Table
1	2	3	4	5
A	B	C	D	E

The approach I vaguely remember creates an array of TRUE/FALSE values the length of the lookup table (5), then somehow combines that with the second row of the table. (Note that the table would normally be vertical, but it's easier to enter here horizontally.)

Thanks in advance.