Index match match

[mole999]

Sure I am doing something fundamentally wrong, trying to match A & B then look up C i.e. 132.25. Can't believe I haven't found the answer. Individually the MATCH work, combining for a single position is eluding me, hence what is currently showing in C1

Excel Workbook
	A	B	C
1	11/07/2019	NORTH EAST	#N/A
2	Abstraction
3	UseDate	SECTOR	RELIEF_HRS
4	08/07/2019	NORTH EAST	0
5	08/07/2019	NORTH WEST	0
6	09/07/2019	NORTH WEST	0
7	10/07/2019	NORTH EAST	0
8	10/07/2019	NORTH WEST	0
9	10/07/2019	SOUTH WEST	0
10	11/07/2019	NORTH EAST	132.25
SQL Test Data

 

[steve the fish]

There are a few ways to do that. Heres one:

=LOOKUP(2,1/(($A$4:$A$10=A1)*($B$4:$B$10=B1)),$C$4:$C$10)

 

[Fluff]

Two other options
=INDEX($C$4:$C$1000,MATCH($A$1&" "&$B$1,$A$4:$A$1000&" "&$B$4:$B$1000,0))
needs CSE entry.
or, a normal formula
=INDEX($C$4:$C$1000,MATCH($A$1&" "&$B$1,INDEX($A$4:$A$1000&" "&$B$4:$B$1000,0),0))

 

[steve the fish]

To add index match match is used to find the intersection of a table where you want to find a locate the position of something (usually) in the first column and then something else in the first row so is unsuitable for what you need here.

 

[Fluff]

To add index match match is used to find the intersection of a table where you want to find a locate the position of something (usually) in the first column and then something else in the first row so is unsuitable for what you need here.

How do you come to the conclusion that it's unsuitable?
Surely the whole point of an index/match formula is to find something in 1 (or more columns) & then return the corresponding value in another column for the same row. Which is exactly what mole is trying to do.

 

[Peter_SSs]

If the combinations in columns A & B are unique and the result column numerical, as it appears is the case for your sample data, you could also use

=SUMIFS(C4:C10,A4:A10,A1,B4:B10,B1)

 

[steve the fish]

How do you come to the conclusion that it's unsuitable?
Surely the whole point of an index/match formula is to find something in 1 (or more columns) & then return the corresponding value in another column for the same row. Which is exactly what mole is trying to do.

Because the title says index match match which is unsuitable.

 

[Fluff]

Good point, I hadn't noticed the thread title.

 

[mole999]

thank you all

 

[Fluff]

You're welcome