Search help

[atxcomputers]

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?

[Stefi]

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
>
>