Index and match?

[fingermouse]

Hi,

I have a problem which I cant solve, will do my best to explain. I think I need something called an index match formula? but I cant get it to work.

I have two excel spreadsheet tabs. Tab 1 contains the data I want to extract, tab 2 will contain the formula

The table below is an extract of tab 1. There are multiple rows for each ref no, of which I have approx 40.

[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD]Ref no[/TD]
[TD][TABLE="width: 129"]
<tbody>[TR]
[TD]EHO[/TD]
[/TR]
</tbody><colgroup><col></colgroup>[/TABLE]
[/TD]
[TD]HGBI[/TD]
[TD]LF[/TD]
[/TR]
[TR]
[TD]NIRHI00004365[/TD]
[TD]76,130[/TD]
[TD]77,060[/TD]
[TD]35.64[/TD]
[/TR]
[TR]
[TD]NIRHI00004365[/TD]
[TD]50,236[/TD]
[TD]51,430[/TD]
[TD]24.05[/TD]
[/TR]
[TR]
[TD]NIRHI00004365[/TD]
[TD]50,247[/TD]
[TD]79,180[/TD]
[TD]36.22[/TD]
[/TR]
</tbody>[/TABLE]

Tab 2 is where I want to have my formula.
[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD]Ref no[/TD]
[TD]EHO[/TD]
[TD]Lookup for HGBI in this column cells[/TD]
[TD]Lookup for LF in this column cells[/TD]
[/TR]
[TR]
[TD]NIRHI00004365[/TD]
[TD]76,130[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]NIRHI00004365[/TD]
[TD]50,236[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]NIRHI00004365[/TD]
[TD]50,247[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]


What I essentially want to do is pull through both the 'HGBI' and 'LF' values from tab 1 where there is a corresponding 'Ref no' AND 'Eligible Heat Output' value in tab 2. Does that make sense? I tried a VLOOKUP but it only pulls out the first value it finds. I have tried reading tutorials on this but find it confusing. If anyone can provide the formula that would be much appreciated. Thanks, Cal.

 

[steve the fish]

Re: need a formula! - index andf match?

Is there only one instance of ref number and EHO combination?

 

[Scott T]

Re: need a formula! - index andf match?

If there are multiple ref number and EHO combinations this will pull the first one only.

Excel 2010
	A	B	C	D
1	Ref no	EHO	HGBI	LF
2	NIRHI00004365	76,130	77,060	35.64
3	NIRHI00004365	50,236	51,430	24.05
4	NIRHI00004365	50,247	79,180	36.22
Sheet6

Excel 2010
	A	B	C	D
1	Ref no	EHO	HGBI	LF
2	NIRHI00004365	76,130	77,060	35.64
3	NIRHI00004365	50,236	51,430	24.05
4	NIRHI00004365	50,247	79,180	36.22
Sheet7

Cell Formulas
Range	Formula
C2	{=INDEX(Sheet6!C$2:C$4,MATCH($A2&$B2,Sheet6!$A$2:$A$4&Sheet6!$B$2:$B$4,0))}
C3	{=INDEX(Sheet6!C$2:C$4,MATCH($A3&$B3,Sheet6!$A$2:$A$4&Sheet6!$B$2:$B$4,0))}
C4	{=INDEX(Sheet6!C$2:C$4,MATCH($A4&$B4,Sheet6!$A$2:$A$4&Sheet6!$B$2:$B$4,0))}
D2	{=INDEX(Sheet6!D$2:D$4,MATCH($A2&$B2,Sheet6!$A$2:$A$4&Sheet6!$B$2:$B$4,0))}
D3	{=INDEX(Sheet6!D$2:D$4,MATCH($A3&$B3,Sheet6!$A$2:$A$4&Sheet6!$B$2:$B$4,0))}
D4	{=INDEX(Sheet6!D$2:D$4,MATCH($A4&$B4,Sheet6!$A$2:$A$4&Sheet6!$B$2:$B$4,0))}
Press CTRL+SHIFT+ENTER to enter array formulas.

 

[fingermouse]

Re: need a formula! - index andf match?

Is there only one instance of ref number and EHO combination?

Hi Steve, thanks for replying - Yes, there will be no instances where theres a duplicate combination of Ref No. and EHO.

 

[steve the fish]

Re: need a formula! - index andf match?

As you are using numbers you can use SUMIFS:

=SUMIFS(Sheet1!$C$2:$C$4,Sheet1!$A$2:$A$4,A2,Sheet1!$B$2:$B$4,B2)
=SUMIFS(Sheet1!$D$2:$D$4,Sheet1!$A$2:$A$4,A2,Sheet1!$B$2:$B$4,B2)

Another potential should they not all be numbers:

=LOOKUP(2,1/((Sheet1!$A$2:$A$4=A2)*(Sheet1!$B$2:$B$4=B2)),Sheet1!$C$2:$C$4)
=LOOKUP(2,1/((Sheet1!$A$2:$A$4=A2)*(Sheet1!$B$2:$B$4=B2)),Sheet1!$D$2:$D$4)

 

[fingermouse]

Many thanks for posting guys, very much appreciated. Took a while, but I have now figured it out!!

 

[Aladin Akyurek]

In C2 of Sheet2 control+shift+enter, not just enter, and copy down:

=IF(COUNTIFS(Sheet1!$A$2:$A$400,$A2,Sheet1!$B$2:$B$400,$B2)>1,"Error: Duplicate record",
    IFERROR(INDEX(Sheet1!$B$2:$B$400,MATCH($B2,IF(Sheet1!$A$2:$A$400=$A2,Sheet1!$B$2:$B$400),0)),"Error: Not Found"))

In D2 of Sheet2, control+shift+enter, not just enter, and copy down:

=IF(COUNTIFS($C2,"Error:*),$C2,INDEX(Sheet1!$C$2:$C$400,MATCH($B2,IF(Sheet1!$A$2:$A$400=$A2,Sheet1!$B$2:$B$400),0)))
​

​

​