Search for value based on column and row header-CodePudding

I have a table of data, and want to retrieve the value specified for a specific row and column header. I've tried to use a nested HLOOKUP and VLOOKUP, but

CodePudding user response：

try:

=VLOOKUP(A10; A2:C9; MATCH(B9; 1:1; 0); 0)

CodePudding user response：

This is overkill for your example, but would be what I'd consider my own "best practice" for heavier Excel lookups.

First off, I'd recommend using the table feature, defining the range of A1:C4 in your example and making sure to confirm first row as headers.

Once you have that in place, in place you can run a lookup easily using header names. XLOOKUP is by far the easiest if you have a version of Office365 or Excel 2021.

Note: Column1 is the default filler name when a table is missing a header value

XLOOKUP solution from your example: =XLOOKUP(A10,Table1[Column1],Table1[Beer '#2],"")

If you're going to be filling in a bunch of combinations, rearranging the order from the original format, or just looking up based on conditions that may not be consistently in the same place - Tables will be a huge help, with XLOOKUP making the whole process human readable.