Look up value from range with multiple rows & columns

untruehdblp002 · September 13, 2021, 7:06am

hi. sorry if this has been asked already. in my 20-min research i’ve found some similar cases but not this particular one. i’m a noob with spreadsheets so this might sound a bit obvious or stupid, so thanks for the patience.

so, i want to find a value in a range that looks exactly like this:

B2:B4 range is text, rest of numbers are divisions. for example, C2 is =B2/C1, D2 is =B2/D1, C3 is =B3/C1 and so on…

the numbers below the table are done with the LARGE function. they’re the 9 biggest numbers sorted from biggest to smallest (sidenote: had to change rankC value by hand instead of the dragging doing it automatically, dunno why)
now i want those numbers to have their respective “lists” defined in A7:A15 range automatically. so, for the first number (8907,00) A7 should show “list3”, for 4567,00 A8 should show “list2” and so on.

is there a way of doing this? tried with lookup, vlookup, hlookup, match+index, nothing worked (might have been me using them wrong). what do you guys think?

karolus · September 13, 2021, 7:15am

Why did you provide a stupid screenshot of your Document ??

Why did you NOT provide the Document itself ?

untruehdblp002 · September 13, 2021, 7:17am

i don’t really know. here’s the document.
NotStupidDocument.ods (12.3 KB)

Lupp · September 13, 2021, 9:30am

Did you delete a column? The words don’t comply with the image.

untruehdblp002 · September 13, 2021, 2:53pm

B2:B4 would be a user input (I’ll polish it once I get some other stuff done) and the rest of the numbers would be dependent on these numbers.

hey thanks so much for the examples. checking them right now.

Lupp · September 13, 2021, 9:27am

On a very low level of conceptual design (below zero in fact) you can do it this way:
disask68056StupidAgain.ods (12.1 KB)

In fact intended but missing assurances (concerning duplicates and monotonic behavior e.g.), and the question if not also the column of a finding should be returned, would require much more…

See also:
disask68056enhancedabit.ods (15.1 KB)

untruehdblp002 · September 13, 2021, 5:01pm

okay, this is way more complex than i anticipated. i cannot express my gratitude towards you, dude. thank you so much for taking the time to do this!

duplicates shouldnt be a concern since this is a template for the d’hont system in elections. as long as 2 adversaries don’t gather literally the same amount of votes, there wouldn’t be any duplicated numbers. same with non-monotonic behavior. as for your last remark, i don’t think it’s necessary, at least not right now, but it’s an interesting idea. could help with other stuff i intend to add eventually. the enhanced example is particularly helpful in this regard. thanks again! marked as solution since…well, it is one lol.

as for your not using large() comment, even if duplicates would not be a thing, what do you sugest using to grab values and sort them from a table like that? in the enhanced (with great additions) you still use large() as your main function.