Hi,

Tried a few functions for this but i cant get it to do what im looking
for!

I have 8 worksheets and Im trying to get a specific reference from A1
on sheet1.

The other 7 pages has all the info in column A

I want B2 to tell me the reference it found out of the text in A1, if
its possible

Example:

sheet1
A1 = abc toy car pt no 789DIN1

on sheet2
A1= 785DIN1955
A2= 786DIN1966
A3= 789DIN1977

This is the closest reference on all of my worksheets so i want

Sheet1
B2 = 789DIN1977

If it cant be done then i would like the same if
Sheet1
A1= abc toy car pt no 789DIN1977

Ive tried using the Search and Match function but i dont think it can
be done using these (cus i dont know enough)

Any help?


--
atxcomputers
------------------------------------------------------------------------
atxcomputers's Profile: http://www.excelforum.com/member.php...o&userid=26852
View this thread: http://www.excelforum.com/showthread...hreadid=469364

First you have to separate the part No from A1 into a different cell, say A2:
=RIGHT(A1,7) (or use another appropriate function if not always 7 characters
are given or type in directly the partial part No to be found).

In Sheet2 sort by column A in descending order!
Then in Sheet1, B2:
=INDEX(Sheet2!A1:A3;MATCH(B1;Sheet2!A1:A3;-1);1)

Replace A3 to An as necessary if you have n rows in Sheet2!

Regards,
Stefi

€žatxcomputers€� ezt Ã*rta:


Hi,

Tried a few functions for this but i cant get it to do what im looking
for!

I have 8 worksheets and Im trying to get a specific reference from A1
on sheet1.

The other 7 pages has all the info in column A

I want B2 to tell me the reference it found out of the text in A1, if
its possible

Example:

sheet1
A1 = abc toy car pt no 789DIN1

on sheet2
A1= 785DIN1955
A2= 786DIN1966
A3= 789DIN1977

This is the closest reference on all of my worksheets so i want

Sheet1
B2 = 789DIN1977

If it cant be done then i would like the same if
Sheet1
A1= abc toy car pt no 789DIN1977

Ive tried using the Search and Match function but i dont think it can
be done using these (cus i dont know enough)

Any help?


--
atxcomputers
------------------------------------------------------------------------
atxcomputers's Profile: http://www.excelforum.com/member.php...o&userid=26852
View this thread: http://www.excelforum.com/showthread...hreadid=469364