Index Match help required?

[Celticshadow]

Hi Forum

I have two workbooks one named Entries and the other named Trainers and I want to lookup the horse A Decent Excuse in the Entries workbook and find the Trainer for Decent Excuse from the Trainers workbook.

I have been using VLOOKUP but it only gives a partial match which results in incorrect results (due to my workbooks extending to some 14k rows with similar names), however I have been searching online and it appears that Index and Match would give me the exact result/match I require.

Thus may I ask forum if they would be kind enough to help me with an index match formula to provide the result I require. I have attached a couple of tables that will hopefully paint a picture of what is required although this a very short cutdown. Thanks in advance.

Regards

ENTRIES

HORSE	DATE	COURSE	TIME	Trainer
A Decent Excuse	01 June 2015	LIS	4:00
A Few Dollars More	04 June 2015	TIP	6:00
A Great View	06 June 2015	LIM	5:45
Abarta	03 June 2015	PUN	7:00

<tbody>
</tbody>

TRAINERS

Horse	Trainer
A Decent Excuse	E M O'Sullivan
A Few Dollars More	A Slattery
A Great View	D Cullen
Abarta	V T O'Brien

<tbody>
</tbody>

 

[svendiamond]

i want to lookup the horse a decent excuse in the entries workbook and find the trainer for decent excuse from the trainers workbook.

Entries

horse	date	course	time	trainer
a decent excuse	01 june 2015	lis	4:00
a few dollars more	04 june 2015	tip	6:00
a great view	06 june 2015	lim	5:45
abarta	03 june 2015	pun	7:00

<tbody>
</tbody>

trainers

horse	trainer
a decent excuse	e m o'sullivan
a few dollars more	a slattery
a great view	d cullen
abarta	v t o'brien

<tbody>
</tbody>

=index(trainers!b:b,match(a2,trainers!a:a,0))

 

[Celticshadow]

	A	B	C	D	E
1	Horse	Date	Course	Time	Trainer
2	A Decent Excuse	01 June 2015 <colgroup><col width="92"></colgroup><tbody> </tbody>	Lis	4.00
3	A Few Dollars More	04 June 2015	Tip	6.00
4	A Great View	06 June 2015	Lim	5.45
5	Abarta	03 June 2015	Pun	7.00

<tbody>
</tbody>

Trainers

	A	B
1	Horse	Trainer
2	A Decent Excuse	E M O'Sullivan <colgroup><col width="92"></colgroup><tbody> </tbody>
3	A Few Dollars More	A Slattery
4	A Great View	D Cullen
5	Abarta	V T O'Brien

<tbody>
</tbody>

Result Required

	A	B	C	D	E
1	Horse	Date	Course	Time	Trainer
2	A Decent Excuse	01 June 2015 <colgroup><col width="92"></colgroup><tbody> </tbody>	Lis	4.00	E M O'Sullivan
3	A Few Dollars More	04 June 2015	Tip	6.00	A Slattery
4	A Great View	06 June 2015	Lim	5.45	D Cullen
5	Abarta	03 June 2015	Pun	7.00	V T O'Brien

<tbody>
</tbody>

Hopefully that may help with regard to my above query.

Regards

 

[Celticshadow]

=index(trainers!b:b,match(a2,trainers!a:a,0))

Hi svendiamond

Many thanks for replying.

Unfortunately that does your formula does not seem to work for me as I wish to look up the horse in the entries workbook and find the trainer associated with it from the trainers workbook. I maybe wrong but your solution appears to be looking at just the trainers workbook. Apologies if I have assumed incorrectly.

Regards

 

[svendiamond]

ADVERTISEMENT

Sorry, I thought they were two different sheets - not two different workbooks. What is the sheet name on the "TRAINERS" workbook? If it were named "Sheet1" this formula should work:

=INDEX('[TRAINERS.xlsx]Sheet1'!$B:$B,MATCH(A2,'[TRAINERS.xlsx]Sheet1'!$A:$A,0))

It is looking at both workbooks... this should be entered into your "ENTRIES" workbook in cell E2.

 

[Celticshadow]

Sorry, I thought they were two different sheets - not two different workbooks. What is the sheet name on the "TRAINERS" workbook? If it were named "Sheet1" this formula should work:

=INDEX('[TRAINERS.xlsx]Sheet1'!$B:$B,MATCH(A2,'[TRAINERS.xlsx]Sheet1'!$A:$A,0))

It is looking at both workbooks... this should be entered into your "ENTRIES" workbook in cell E2.

Hi svendiamond

Sincere apologies for lateness in replying, I am very grateful for your help and am happy to say that your solution worked perfectly. Top class as usual form forum members.

Regards

 

[Babushki87]

Hi,

I need to do an index match formula with 3 criterias can you provide an example to help?

Thanks.

 

[svendiamond]

Hi,

I need to do an index match formula with 3 criterias can you provide an example to help?

Thanks.

Babushki, make a new post with your question.