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