3 ranges reference, Display cell content from non sort List, depending from Search Cells

[AndyJR]

Hi,
Seems weird the tread title, sorry about that but i have no idea how to point the problem,
because i don't know, please I need info about which formula to use to do the follow:

On my sheet I'm Using 3 Ranges.

Range 1, Search Cells,
4 cells located on (K1 to N1). (in fact there are not 4, are 8 digits)

Range 2, List of integer to be searched
100 cells located Column A (A3:A100).

Range 3, Display Result of Searched Cells,
4 cells located on (T4 to W4).

The problem :

I need to get the number searched and displayed in Range 3 (T4 to W4) in the order appear in Range 2 (List of integer to be searched)
not the sorted way as I Input it in Range 1 (Search Cells)

example

1-. In Search Range 1 (K1 to N1) I Input on sorted way -> 01, 15, 85, 94

2-. On Range 2 (A3:A100) The List appear like :

07
90
15*
02
03
94*
20
18
13
11
85*
19
01*
04
etc, etc,etc

3-. On Range 3, (T4 to W4) Display Result of Searched Cells, i need to get Displayed the Numbers searched
in the same order that appear on the List -> 15, 94, 85, 01


I tried with differents singles and concatenated formulas, as Index, Match and so on, i probably is a easy formula but still didn't got it.

P.S actually i'm using cell reference Indirect, displaying numbers Sorted.


Thank you


AndyJr

 

[AndyJR]

Please "BUMP"

 

[AndyJR]

"bump"

 

[XOR LX]

Hi.

In K4, array formula**:

=INDEX($K$1:$N$1,MATCH(SMALL(MATCH($K$1:$N$1,$A$3:$A$100,0),COLUMNS($A:A)),MATCH($K$1:$N$1,$A$3:$A$100,0),0))

Copy across as required.

Note that, if just one of the four values is not found within the list, all four formulas will return an error. If you do not wish this to be the case then please give an indication of what should be the results in such cases.

Regards


**Array formulas are not entered in the same way as 'standard' formulas. Instead of pressing just ENTER, you first hold down CTRL and SHIFT, and only then press ENTER. If you've done it correctly, you'll notice Excel puts curly brackets {} around the formula (though do not attempt to manually insert these yourself).

 

[AndyJR]

Hi XOR LX,

Thank you so much!!!

just in case if reference is changed, i added the iferror formula and it work perfect !

=IFERROR(INDEX($K$1:$M$1,MATCH(SMALL(MATCH($K$1:$N$1,$A$3:$A$100,0),COLUMNS($A:A)),MATCH($I$1:$M$1,$A$3:$A$100,0),0)),"")

Thanks again!!!


AndyJr

 

[XOR LX]

You're welcome!

Cheers