Index/Match to return an Offset value

[JeffGrant]

Hi All,

I cant seem to get this right. (bugga)

A	B	C	D	E	F	G	H	I
								Horse
1. Chuck A Luck (7)	8.50 7.00 6.00 5.50 5.00 4.80	4.8	SP	TF	20.1			1. Chuck A Luck (7)
61.5kgJ: Keshaw Dhurun (a2)1050xT: N D Parnham	1.9	3.3			2. Grand Design (2)
2. Grand Design (2)	4.20 4.40 4.60 4.50 4.60	4.6	SP	TF	35.4			3. Vital Flirt (4)
58.5kgJ: Brad Rawiller0x346T: Roy Rogers	1.85	4.8			4. Celtic Court (10)
3. Vital Flirt (4)	13.00 14.00 15.00 17.00 18.00	18	SP	TF	18			5. Firesale (9)
58.5kgJ: Brad Parnham4x970T: Ms K Farrell	4.8	1.6			6. Dipara (5)
4. Celtic Court (10)	5.00 5.50	5.5	SP	TF	30.1			7. Great Charade (1)
57kgJ: William Pike644x1T: Roy Rogers	2.05	TBA			8. Can He Hussel (8)
5. Firesale (9)	14.00 15.00 19.00	19	SP	TF	38.4			9. Igarashi (3)
56.5kgJ: Ben Kennedy869x8T: Brett Pope	5	4.8			10. Watch Me Ney Ney (6)
6. Dipara (5)	10.00 11.00 12.00	12	SP	TF	115.4			0​
56kgJ: Jason Whiting41x04T: K J Mifflin	3.5	4.8			0​
7. Great Charade (1)	4.20 4.00 4.20 4.40 4.50 4.60	4.6	SP	TF	30.1			0​
56kgJ: Joseph Azzopardi10x57T: T M Andrews	1.85	4.8			0​
8. Can He Hussel (8)	26.00 31.00 34.00	34	SP	TF	34			0​
54kgJ: Laqdar Ramoly (a1.5)0344xT: Meryl Hayley	7.5	TBA			0​
9. Igarashi (3)	8.00 7.00 7.50 8.00 8.50 9.00	9	SP	TF	19.2			0​
54kgJ: Ms Holly Watson (a3)60346T: D T Mcauliffe	2.9	4.8			0​
10. Watch Me Ney Ney (6)	14.00 14.00 11.00 8.00 8.50 8.00	8	SP	TF	38.4			0​
54kgJ: Clint Johnston-Porter3813xT: D L Morton	2.7	TBA			0​
								0​
								0​

in this table, the data is grouped in pairs of two rows.

If Match column I to Column A, I can use Index to return Column C. No Drama.

So I2 matches A2, returns C2 & I4 matches A4, returns C4. All good.

However when I match I2 to A2, I would like Index (or offset) to return C3 ie same column, next row.

Likewise match I4 to A4 and return C5 etc

Clearly I haven't mastered Index/Match enough and seek help fromthe good people of this forum.

Any guidance would be greatly appreciated.

Thanks Jeff.

 

[Marcelo Branco]

Maybe...

=INDEX(C$2:C$1000,MATCH(I2&"*",A$2:A$100,0)+1)

M.

 

[JeffGrant]

thanks. works perfectly. how does the &"*" part work? I have never seen that before.

cheers

 

[Marcelo Branco]

You're welcome. Thanks for the feedback.

* is a wildcard character that represents any number of characters
Take a look at

Excel Wildcard Characters - Why Aren't You Using These?

There are three Excel wildcard characters (asterisk, question mark, and tilde) that can get some amazing stuff done in Excel. Here are some examples.

trumpexcel.com

M.