How to look up a value from a database range with named rows, columns?

[artfulrobot1]

I have data like

                Education       Hats
Wilma           PhD             5
Betty           Degree          2 

It’s a database range called People

I want to write a formula that works like =LOOKUP(People, "Wilma", "Education") → PhD or =LOOKUP(People, "Betty", "Hats") → 2

The reason I need this is because I need to bring in looked-up values from the database range into a different sheet; in that sheet instead of “Wilma” or “Hats” there will be a cell reference to a cell containing “Wilma” or “Hats”. Previously I have done this with VLOOKUP to identify the row and then a numerical index that I have to remember relates to Hats. However this has proved brittle, e.g. column insertion.

The only answer I found was this: Lookup by column and row values

which suggests a solution like:

=INDIRECT(ADDRESS(
  MATCH("Wilma";namedRangeForRowNames);
  MATCH("Hats",namedRangeForColNames)
))

This is OK, though it requires two named ranges and is verbose, which quickly clutters things up - a formula I need to write has dozens of these references in it and will become impossible to look at with all this cruft.

I thought DGET() or DVALUE might help but these seem to be something a lot more complex.

I feel like my need here is very common and simple and so I’m hoping that there’s a simple answer!

[JohnSUN]

I remember this discussion a few years ago What is the best way to get the value from a rectangular table?

[shantanuo]

Convert your data to pandas dataframe using this extension: https://extensions.libreoffice.org/en/extensions/show/99231

[koyotak]

Just a reminder: LOCKUP and MATCH work well with numbers, but flawed with words.

[karolus]

@koyotak

LOCKUP and MATCH work well with numbers, but flawed with words

@koyotak Sorry, but you telling nonsense!

[karolus]

Hallo

=INDEX(People; MATCH("Wilma";namedRangeForRowNames;0); MATCH("Hats";namedRangeForColNames;0)

and of course the literal “Wilma” and “Hats” can be replaced by Cellreferences

[PKG]

=XLOOKUP("Wilma";namedRangeForRowNames;XLOOKUP("Hats";namedRangeForColNames;People))

[artfulrobot1]

I think this is the most elegant of solutions offered, thank you, though I still think it has a lot of problems:

1. Requires three named ranges!
2. Still very verbose
3. Cannot handle errors: XLOOKUP has a “not found” param, but it fails because the inner xlookup is expected to output a range that has the same length as namedRangeForRownames, which we assume to be variable.