CODEWITHSUNDEEP
google-sheets
Mike4President
Mike4President

Reputation: 23

Google Sheets, returning a text value based on relative rank

I'm trying to create a relative ranking structure based on cell values within a repeating range.

I have ranked the text values accordingly. Text Ranks

I then have a sheet with columns that contain subsets of these text values like so; Text Value Table

What I am trying to accomplish, is that when a Rank of 1 is entered into a cell in row A, it will return the highest value of that row based on the column values in row B. So as an example;

If 1 was entered into cell A2, it would return "Text 1" in B2. If 2 was entered in A2 it would return "Text 3" in B2. If 3 was entered in A2 it would return "text 5". If any other rank (4-20) was entered B2 would remain blank. The same would happen in Row 3 except it would consider "Text 2" as rank 1, "Text 3" as rank 2, and "Text 4" as rank 3.

I can make this work in Excel with XLOOKUP but I've been unable to solve the problem with Google Sheets, any help here would be extremely appreciated!

Upvotes: 1

Views: 774

Answers (2)

Aresvik
Aresvik

Reputation: 4630

If you're OK copying down the formula, then this should work:

=iferror(index(split(textjoin(char(6655),true,$C2:$G2),char(6655)),$A2),"")

The values on the row are joined together using TEXTJOIN, separated by CHAR(6655) which is a character you wouldn't expect to have in your text. The true in the TEXTJOIN removes blank cells. Then SPLIT breaks the text into separate cells using CHAR(6655).

INDEX then gets the corresponding cell value dependent on your Rank value in column A.

IFERROR displays nothing ("") if the Rank value is greater than the number of cells of data.

enter image description here

Upvotes: 0

NightEye
NightEye

Reputation: 11214

Try this one:

Formula (B7):

=IFERROR(INDEX(ARRAYFORMULA(VLOOKUP(SORT(VLOOKUP(TRANSPOSE(C7:7), {Sheet2!$B$2:$B$21,Sheet2!$A$2:$A$21}, 2, FALSE)), Sheet2!$A$2:$B$21, 2, FALSE)), A7), "")

Sheet2:

sheet2

Output:

output

Formula steps:

  1. TRANSPOSE the row into column to be able to use VLOOKUP and compare it to the Sheet2 Table.
  2. Use VLOOKUP to return the rank.
  3. SORT the rank.
  4. VLOOKUP again to look the values in the Sheet2 and now return the text values
  5. At this point, we now have the list of sorted text values based on our row values, now we use the given rank and get the sorted text value based on rank using INDEX
  6. If it returns #N/A, return blank instead.

Step by step visualization of the formula for the 7th row:

visualization

Upvotes: 1

Related Questions