#1

Okay, I’m looking for a formula. I’ve got a sheet with headings along row 2, and weeks down column A (from A3 downwards). So a pretty simple setup, A, B, C etc along the top, 1, 2, 3, etc down the side.

I want to find the value that matches both the week and the heading from another sheet, so in B3 I want to look for a value that matches Heading B and Week 1, and so on. In this other sheet which already contains the values the week numbers are in column A and the headings are down column B, with the values I want in column c.

It’s something I feel like must be fairly simple, but I’m trying IF functions and VLOOKUPs and not quite getting there.

#2

index and match

3 Likes
#3

Okay, googling this now.

#4

*game, set and

2 Likes
#5

Sorry I didn’t explain it. It’s the combination of these two functions that allow you to extend lookups firstly in both directions and secondly with multiple criteria.

#6

If you’re able to add another column in the data source, joining the two you can do it with INDEX.

in data sheet, column D = A1 & “|” & B1

In the lookupsheet, B3 = INDEX(Source!$C:$C, Match(B$2 & “|” & $A3, Source!$D:$D,1))

#7

ignore these guys they’re wrong.

can you explain the problem again please?

#8

Problem here is that he’s trying to transform a list into a matrix, not the other way round, so you can’t use MATCH on both columns.

If the data you want to bring back is numeric, you can just use
=SUMIFS(Source!$C:$C, Source!$A:$A, $A3, Source!$B:$B, B$2)

#9

This seems to work, thanks.

#10

No problem.

Thinking about it, if you can add a guide column like that, you could just put it in front of the value and use VLOOKUP

#11

Hate being late to an excel thread, and index match is my favourite.

If you want to turn a matrix into a list there is another useful little trick

Press alt then d then p and it will bring up a wizard, click something like ‘consolidate multiple ranges’ add the original table range, click finish and it will give you a pivot table that resembles the original, double click grand total and it will create a new table in the list format.

This was my go to unpivotting method before power query came along

#12

Recently did 2 Excel courses (Intermediate Levels 1 and 2 or some shit)) which told me how to do VLOOKUPs and COUNTIFs and what not. It’s literally changed my life. Especially VLOOKUP. Absolute gamechanger.

Spent a LOT of time in these courses learning about Range Names mind. Not sure why - pretty sure no-one uses them. 10 minutes on that and move on surely.

#13

Very useful range names. Nothing better if you want to a) make your spreadsheet unintelligible to anybody other than yourself or b) annoy someone needlessly.

#14

Big fan of named ranges based on a dynamic formula