Search for value based on column and row header

Question

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 the only examples I've found rely on a helper column that introduce new challenges.

I have a feeling this is possible using INDEX(MATCH), but can't quite seem to work it out.

I have a screenshot of what I'm looking for.

Update: I'm looking for a formula that will work as I had more Beers across the columns (it could be 2, it could be 100). And also needs to work where I would add more rows of ingredients.

Or, Double XLOOKUP() in O365, lot of queries on this in SO, have you tried searching them ? — Mayukh Bhattacharya
– Mayukh Bhattacharya, Commented Apr 13, 2022 at 19:44
... noting that XLOOKUP doesn't exist in Google Sheets yet - best to only use appropriate tags if you want a solution for a specific platform. Microsoft Excel is not Google Sheets. — BigBen
– BigBen, Commented Apr 13, 2022 at 19:45
=SUMPRODUCT(B2:C4*(A2:A4=A10)*(B1:C1=B9)) would work in both GS and Excel. Both apps have their own unique functions that could make it work. As @BigBen mentioned, choose one and stick with it. — JvdV
– JvdV, Commented Apr 13, 2022 at 20:21

player0 · Accepted Answer · 2022-04-13 21:55:41Z

3

try:

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

edited Apr 13, 2022 at 21:55

answered Apr 13, 2022 at 20:20

player0

131k14 gold badges91 silver badges149 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

fakeguybrushthreepwood Over a year ago

I understand this answers this particular situation, but the challenge is I might end up having 10,15,100 different beers... I need to update this in the original description. Sorry about not specifying that.

Dean Meehan · Accepted Answer · 2024-01-03 12:58:14Z

This can be done using both the index and match functions within Google Sheets.

Using the example below as the data where the date field is variable as well as the billing_team:

If I want to get the value for team2 on 2023-12-31 I would use the functions:

=index(
 data!A1:C4,
 match("team2",data!A1:A,0),
 match(DATEVALUE("2023-12-31"),data!A1:1)
)

Returns: 3,582.22

function	explained	value returned
`data!A1:C4`	The range of data to search including headings	3,582.22
`match("",data!A1:A,0)`	Returns the row that `team2` was found	2
`match(DATEVALUE("2023-12-31"),data!A1:1)`	Returns the column where the date `2023-12-31` is found. DATEVALUE() is not required if just matching a non-date string	2

eg. The query is parsed as:

=index( data!A1:C4, 2, 2 )

Returns the 2nd Row and Column of the Values within A1:C4 of the sheet `data`

ghostrobot · Accepted Answer · 2022-04-13 20:52:41Z

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.

Collectives™ on Stack Overflow

Search for value based on column and row header

3 Answers 3

1 Comment

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

1 Comment

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related