Excel help please

Social
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

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.