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))

First name	Last Name	Salary
James	Smith	64710
James	Anderson	122234
James	Clark	34556
John	Lewis	1223434
John	mark	122323
Mark	Reed	1224354

<tbody>
</tbody>

I need

First Name	Last Name	Salary
James	Clark	?

<tbody>
</tbody>

 

[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

Row\Col	A​	B​	C​	D​	E​	F​	G​	H​
1​	First name	Last Name	Salary			first Name	Last Name	Salary
2​	James	Smith	64710​			James	Clark	34556​
3​	James	Anderson	122234​
4​	James	Clark	34556​
5​	John	Lewis	1223434​
6​	John	mark	122323​
7​	Mark	Reed	1224354​

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.