INDEX MATCH with Sorting

[Azshara]

Here I am again guys . I just want to ask for help about some formula

First of all, here's my formula for C11 wherein C10 is the filter/search/input data
=LARGE(INDEX($C$3:$E$8,MATCH($C$10,$C$2:$E$2,0),0),ROWS($A$1:A1))

for B11 to 13
=INDEX($B$3:$B$7,MATCH($C$10,$C$2:$E$2,0),0)

Unfortunately, this is not the result I want to show

Please see my attachment . Hope someone helps me TY XD

[keme]

Could you explain what you try to achieve?

I suspect that you have mismatched parentheses, and possibly also swapped row and column numbers for the INDEX() function, but I am really not sure what you are aiming at.

[Lupp]

First of all:
Post formulae that can be parsed everywhere by any spreadsheet software. Did you copy your formulae from an Excel sheet or from where? The one parameter delimiter accepted in AOO and LibO everywhere is the semicolon.
Concerning issues with the functionality of spreadsheets always attach "the real thing": A spreadsheet document demonstrating your problem. An image rarely can help the helper to understand the case.

Second:
Why do you expect the subexpression

Code: Select all

MATCH($C$10;$C$2:$E$2;0)

to return a usable result? Match can only return one index for whatever it found - or an error. An element of a 2D-array is not characterised by one index.

Third:
LARGE() also can only return one found number. If you try to get a sorted result using LARGE() with tricks you need array-evaluation anyway. I seriously dissuade from such an approach. You would (e.g.) never be able to also get accompanying data.

Finally:
Do as keme suggested. Explain what you actually want to achieve, and hope for expert suggestions how it might be done.

[Azshara]

Could you explain what you try to achieve?

I suspect that you have mismatched parentheses, and possibly also swapped row and column numbers for the INDEX() function, but I am really not sure what you are aiming at.

Hi, Keme thanks for your reply. What I'm trying to get is the result of row 17 to 20
When I type any of LABEL, MISSING or OTHER to C10 the result should go through either column C3:C8 or D3:D8 or E3:E8 not in every row (If you get what I mean XD)
And the sorting would be from Highest figures to lowest in column C17 to C20

Please check my file I just converted it to xlsx for you to try XD

[Azshara]

First of all:
Post formulae that can be parsed everywhere by any spreadsheet software. Did you copy your formulae from an Excel sheet or from where? The one parameter delimiter accepted in AOO and LibO everywhere is the semicolon.
Concerning issues with the functionality of spreadsheets always attach "the real thing": A spreadsheet document demonstrating your problem. An image rarely can help the helper to understand the case.

Second:
Why do you expect the subexpression

Code: Select all

MATCH($C$10;$C$2:$E$2;0)

to return a usable result? Match can only return one index for whatever it found - or an error. An element of a 2D-array is not characterised by one index.

Third:
LARGE() also can only return one found number. If you try to get a sorted result using LARGE() with tricks you need array-evaluation anyway. I seriously dissuade from such an approach. You would (e.g.) never be able to also get accompanying data.

Finally:
Do as keme suggested. Explain what you actually want to achieve, and hope for expert suggestions how it might be done.

Hi Lupp, apologies for the confusion what I'm dealing with is the highlighted in green results, and yes I just searching for formulae online coz it I'm not an excel expert and thank you for your kindness

[MrProgrammer]

When I type any of LABEL, MISSING or OTHER to C10 the result should go through either column C3:C8 or D3:D8 or E3:E8 not in every row (If you get what I mean XD). And the sorting would be from Highest figures to lowest in column C17 to C20

I don't understand how one can expect to sort six values (cells D3:D8) into four cells (C17:C20). And your abbreviation XD needs explanation. However, I believe you should read [Tutorial] Sorting and Filtering data with formulas. It has examples for you to study.

Please check my file I just converted it to xlsx for you to try

This is an OpenOffice forum. Please attach ODS format, not XLSX format; the latter may not convert properly when read by OpenOffice.

If this solved your problem please go to your first post use the Edit button and add [Solved] to the start of the title. You can select the green checkmark icon at the same time.

[Azshara]

When I type any of LABEL, MISSING or OTHER to C10 the result should go through either column C3:C8 or D3:D8 or E3:E8 not in every row (If you get what I mean XD). And the sorting would be from Highest figures to lowest in column C17 to C20

I don't understand how one can expect to sort six values (cells D3:D8) into four cells (C17:C20). And your abbreviation XD needs explanation. However, I believe you should read [Tutorial] Sorting and Filtering data with formulas. It has examples for you to study.

Please check my file I just converted it to xlsx for you to try

This is an OpenOffice forum. Please attach ODS format, not XLSX format; the latter may not convert properly when read by OpenOffice.

If this solved your problem please go to your first post use the Edit button and add [Solved] to the start of the title. You can select the green checkmark icon at the same time.

HAHAHA apologies it seems I getting a big mess XD

OK let me clear hope I can explain it clearly XD

This are the supplied data

1.png (11.23 KiB) Viewed 2003 times

And here is my expected results

1.png (6.73 KiB) Viewed 2003 times

So, C16 (where it see's the word MISSING) is an input or search or filtered data (as I call it)
The data what I am looking for is the LARGEST among whenever I will input in C16 range of C2:E8 (ofc just only LABEL, MISSING or OTHER as the header) and the result I'll get should be like this

1.png (6.73 KiB) Viewed 2003 times

The thing here is, is it possible to have a formula with 2 results just like what I've show
HAHAHA it makes me crazy hope you understand LOL
I attached my sample XD

[Lupp]

-1-
The attempts shown in the posts of the OQ and in his first attachment(.xlsx) are basically inapt because they try to apply MATCH() to a 2-dimesional lookup range. The second attachment(.ods) doesn't contain any formulas at all.

-2-
Against reason sorting by formulas based on LARGE(), SMALL(), RANK() is described or even recommended again and again.
Even where actually applicable it will be inefficient mostly. Applicable it is if ...
-a- The only goal is the sorted output of the numeric values from the range delivered to the above mentioned functions,
-a- i.e. no accompanying data need to be sorted.
OR
-b- There is an undoubtable assurance that the numeric values the sorting is based on do not contain duplicates.

The reasons for what -2- names the attempts "against reason" you can find in my posts here: search.php?keywords=sort+small+large+rank&fid%5B0%5D=9 (and in many püosts by others, of course).

Well, if doubtable complications are accepted (tricky disambiguation) it can be done. To do it actually this way can only be tolerated if the user doing it understands what he is doing to the detail.

[Azshara]

-1-
The attempts shown in the posts of the OQ and in his first attachment(.xlsx) are basically inapt because they try to apply MATCH() to a 2-dimesional lookup range. The second attachment(.ods) doesn't contain any formulas at all.

-2-
Against reason sorting by formulas based on LARGE(), SMALL(), RANK() is described or even recommended again and again.
Even where actually applicable it will be inefficient mostly. Applicable it is if ...
-a- The only goal is the sorted output of the numeric values from the range delivered to the above mentioned functions,
-a- i.e. no accompanying data need to be sorted.
OR
-b- There is an undoubtable assurance that the numeric values the sorting is based on do not contain duplicates.

The reasons for what -2- names the attempts "against reason" you can find in my posts here: search.php?keywords=sort+small+large+rank&fid%5B0%5D=9 (and in many püosts by others, of course).

Well, if doubtable complications are accepted (tricky disambiguation) it can be done. To do it actually this way can only be tolerated if the user doing it understands what he is doing to the detail.

Apologies Lupp the formula was in row 10-13 i just hide it because i've screenshot XD

[Azshara]

Finally, I got it to work but the lookup is horizontally not vertically.

[Lupp]

Well, I knew your formulas anyway, and I critisised them in detail (partly by pointing you to older threads). They simply are inapt beyond improvement.

If you now claim you "got it to work", I have to assume you didn't read the criticism or you didn't understand it. If you doubt my statments - what, of course, you are free to do - it is necessary to refute my claims or to cease continuing the discussion as long as not someone else takes over in my place.

If you are sure to need sorting by formulas for a small collection of data in a fix range in contiguous rows, you can do it either introducing a few helper columns or delegating the task to user code. Anyway you need to understand the solution because otherwise you may run in a deadlock situation as soon as you need to enhance/up-scale your sheets. Even worse: You may introduce undetected errors with every tiny change in the apparatus. Modifications of a solution trying to make it "fool-proof" may result in extra problems - and inefficiency.

[Villeroy]

topic_98317.ods

nother pivot

(20.33 KiB) Downloaded 112 times