Index match ascend between 2 sheets

[BoZD]

I'm not finding much on INDEX MATCH ASCEND BETWEEN 2 SHEETS and 2 COLUMNS, other then tediously do each pair of columns VBA Asecend, which is what I am trying to avoid.

How is the Formula to do this please ?

Sheet1 Column A2:A26 and Sheet1 Column B2:B26 is the source

Sheet2 Column A2:A26 and Sheet2 Column B2:B26 is the the Ascended of Sheet 1

The Reference to Ascend values is from Sheet 1 B2:B26, but must match the same in Column A

So if A2: A26 is, 1,2,3,4,5....and

B2:B26 is and asortment of negative, 0 and positive values, then the lowest value
is to be shown first on Sheet2.
-----------
Sheet 1
A B
------------
1. 10.
2. 5
3. 0
4. -15
5. 3
then Sheet 2's INDEX is:
-----------
Sheet 2
A B
------------
4. -15
3. 0
5. 3
2. 5
1. 10

If blank, show "",


Thanks

[FDibbins]

It might help if you uploaded a sample workbook, showing what you are working with and what you want?

[BoZD]

Sure, No Problem, Thanks.
I've renamed the sheets

[Saarang84]

Hi,

In the 2nd sheet, in column C, you can use either of the below formulas, they will give you identical result.

Please Login or Register  to view this content.

or

Please Login or Register  to view this content.

Is this what you need?

[BoZD]

Thanks for replying Saarang84 ,
I'm not sure I understand the formula, I've uploaded a workbook since as suggested, showing
precisely how the result has to be.

[Punnam]

Hi BOZD,

I have observed the value of 5 in "INDEX FORMULA HERE" is 9.170 & the same is not present in "Data" Sheet.

and for the rest
=INDEX(DATA!$C$2:$C$13,MATCH('INDEX FORMULA HERE'!B2,DATA!$B$2:$B$13,0))

This is working fine.

Punnam

[Saarang84]

Thanks for replying Saarang84 ,
I'm not sure I understand the formula, I've uploaded a workbook since as suggested, showing
precisely how the result has to be.

Did you try any one of the formulas in my post #4 ?

[humdingaling]

see attached file

Small/row to get ranking/ascend

Please Login or Register  to view this content.

index/match to get number

Please Login or Register  to view this content.

C266 should be C26 but just means longer array

[BoZD]

Thank for your replies, but not working as I thought.

Have added another workbook with macro showing the exact result in sheet 2

Just F8 macro, and it's the ascending order that's required, but with formula.

The other reason I need within the formula range, if blank, then show blank, because not
always the entire range is populated.
Currently there are 12 ( 24 ) cells populated in this example, but at other times there are
more or less, but always no more than 26.

[BoZD]

Worked it out, thanks all, hope this helps others

[BoZD]

slight oversight

corrected formula

Please Login or Register  to view this content.

It still does not get the value from Sheet 1, I get an error, but it will do for now.