Index Match function

[abhishekkhosla]

Hi,

I am trying to learn how to use index match function when there is multiple variables. I am getting NA error below is the screen shot can somebody explain where i am going wrong and what i need to be careful when i am using the formula. I am using ctrl+shift+enter to sun this formuls

Forumla : =INDEX(C2:C8,MATCH(1,(F2=A2:A8)*(F3=B2:B8),0))

[TABLE="width: 500"]
<tbody>[TR]
[TD]First name[/TD]
[TD]Last Name[/TD]
[TD]Salary[/TD]
[/TR]
[TR]
[TD]James[/TD]
[TD]Smith[/TD]
[TD]64710[/TD]
[/TR]
[TR]
[TD]James[/TD]
[TD]Anderson[/TD]
[TD]122234[/TD]
[/TR]
[TR]
[TD]James[/TD]
[TD]Clark[/TD]
[TD]34556[/TD]
[/TR]
[TR]
[TD]John[/TD]
[TD]Lewis[/TD]
[TD]1223434[/TD]
[/TR]
[TR]
[TD]John[/TD]
[TD]mark[/TD]
[TD]122323[/TD]
[/TR]
[TR]
[TD]Mark[/TD]
[TD]Reed[/TD]
[TD]1224354[/TD]
[/TR]
</tbody>[/TABLE]

I need

[TABLE="width: 500"]
<tbody>[TR]
[TD]First Name[/TD]
[TD]Last Name[/TD]
[TD]Salary[/TD]
[/TR]
[TR]
[TD]James[/TD]
[TD]Clark[/TD]
[TD]?[/TD]
[/TR]
</tbody>[/TABLE]

 

[VBA Geek]

In your example above I would use SUMIFS

 

[Aladin Akyurek]

If F2 = James and F3 = Clark, the formula you have, confirmed with control+shift+enter, not just enter, wiil give:

34556

 

[abhishekkhosla]

Hi I am trying it but it is giving me NA

 

[abhishekkhosla]

Can you please share how you will use sumif in a lookup function

 

[AhoyNC]

Maybe in your formula F3 should be G2?

Excel Workbook
	A	B	C	D	E	F	G	H	I	J
1	First name	Last Name	Salary			First Name	Last Name	Salary		SUMIFS
2	James	Smith	64710			James	Clark	34556		34556
3	James	Anderson	122234
4	James	Clark	34556
5	John	Lewis	1223434
6	John	mark	122323
7	Mark	Reed	1224354
Sheet

 

[OGCV1]

Can you please share how you will use sumif in a lookup function

VBA Geek is suggesting:
=SUMIFS(C1:C6,A1:A6,E1,B1:B6,F1)

Which when E1 = James & F1 = Clark will give 34556

 

[Aladin Akyurek]

Hi I am trying it but it is giving me NA

[Table="width:, class:grid"][tr][td]Row\Col[/td][td]

A​

[/td][td]

B​

[/td][td]

C​

[/td][td]

D​

[/td][td]

E​

[/td][td]

F​

[/td][td]

G​

[/td][td]

H​

[/td][/tr][tr][td]

1​

[/td][td]First name[/td][td]Last Name[/td][td]Salary[/td][td][/td][td][/td][td]first Name[/td][td]Last Name[/td][td]Salary[/td][/tr]
[tr][td]

2​

[/td][td]James[/td][td]Smith[/td][td]

64710​

[/td][td][/td][td][/td][td]James[/td][td]Clark[/td][td]

34556​

[/td][/tr]
[tr][td]

3​

[/td][td]James[/td][td]Anderson[/td][td]

122234​

[/td][td][/td][td][/td][td][/td][td][/td][td][/td][/tr]
[tr][td]

4​

[/td][td]James[/td][td]Clark[/td][td]

34556​

[/td][td][/td][td][/td][td][/td][td][/td][td][/td][/tr]
[tr][td]

5​

[/td][td]John[/td][td]Lewis[/td][td]

1223434​

[/td][td][/td][td][/td][td][/td][td][/td][td][/td][/tr]
[tr][td]

6​

[/td][td]John[/td][td]mark[/td][td]

122323​

[/td][td][/td][td][/td][td][/td][td][/td][td][/td][/tr]
[tr][td]

7​

[/td][td]Mark[/td][td]Reed[/td][td]

1224354​

[/td][td][/td][td][/td][td][/td][td][/td][td][/td][/tr]
[/table]

In H2 control+shift+enter, not just enter:

Either...

=INDEX($C$2:$C$7,MATCH($F2,IF($B$2:$B$7=$G2,$A$2:$A$7),0))

Or...

=INDEX(C2:C8,MATCH(1,(F2=A2:A8)*(G2=B2:B8),0))

Note that these refer to G2, not to F3 as you accidentally might have done.