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!