match and index newbie

[avk_curr]

Hi All,

Probably a simple question, but I need to use a match and index, but I've never used them before.

I need to look up the years and then look up the month and provide a value
For example
1 year and 5 months would produce 1.1556

[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD]Years[/TD]
[TD]Monnth[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD]0[/TD]
[TD]1[/TD]
[TD]3[/TD]
[TD]4[/TD]
[TD]5[/TD]
[TD]6[/TD]
[/TR]
[TR]
[TD]0[/TD]
[TD]0[/TD]
[TD].0722[/TD]
[TD].1444[/TD]
[TD].2167[/TD]
[TD].2889[/TD]
[TD].3611[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD].8667[/TD]
[TD].9389[/TD]
[TD]1.00111[/TD]
[TD]1.0834[/TD]
[TD]1.1556[/TD]
[TD]1.2278[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]6[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

 

[JoeMo]

The first MATCH returns the row number. The second MATCH, the column number.
Domain

	A	B	C	D	E	F	G	H	I	J
Years	Monnth							Year
		Month
	Value

<colgroup><col style="font-weight:bold; width:30px; "><col style="width:40px;"><col style="width:56px;"><col style="width:46px;"><col style="width:53px;"><col style="width:46px;"><col style="width:46px;"><col style="width:46px;"><col style="width:22px;"><col style="width:48px;"><col style="width:49px;"></colgroup><tbody>
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]1[/TD]

[TD="align: right"]1[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]2[/TD]

[TD="align: right"]0[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]3[/TD]
[TD="align: right"]4[/TD]
[TD="align: right"]5[/TD]
[TD="align: right"]6[/TD]

[TD="align: right"]5[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]3[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0.072[/TD]
[TD="align: right"]0.1444[/TD]
[TD="align: right"]0.217[/TD]
[TD="align: right"]0.289[/TD]
[TD="align: right"]0.361[/TD]

[TD="align: right"]1.1556[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]4[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]0.8667[/TD]
[TD="align: right"]0.939[/TD]
[TD="align: right"]1.0011[/TD]
[TD="align: right"]1.083[/TD]
[TD="align: right"]1.156[/TD]
[TD="align: right"]1.228[/TD]

</tbody>

Spreadsheet Formulas

Cell Formula J3 =INDEX($A$2:$G$4,MATCH(J1,A2:A4,0),MATCH(J2,A2:G2,0)) <tbody> </tbody>

<tbody>
</tbody>

Excel tables to the web >> Excel Jeanie HTML 4

 

[markmzz]

Hi!

You can try this too (with JoeMo's layout):

=VLOOKUP(J1,$A$3:$G$9,MATCH(J2,$A$2:$G$2,0),0)

or

=HLOOKUP(J2,$B$2:$G$9,MATCH(J1,$A$2:$A$9,0),0)

Markmzz

 

[avk_curr]

Thank you, I think I was missing the MATCH(J1,A2:A4,0),MATCH(J2,A2:G2,0)) I was referencing the whole table A2:G4 each time,
Need to learn match and index better

 

[avk_curr]

interesting. Thanks

 

[AhoyNC]

Here is a link on using INDEX / MATCH.

https://www.youtube.com/watch?v=u-8D2G0y9EM

 

[markmzz]

interesting. Thanks

You are welcome.

Markmzz