Cell search

[AndrewD04]

Hi,

I am struggleing with an excel query and would love some help.

I have a number say 2578654A in Cell A1 and a list of numbers on Sheet 2 which correlate to the numbers in Cell A1 above.

I want a formula to search the Numbers in A1 for matching numbers from the list and enter that number in B2 and so on.

For example it searches and finds 25 so it puts that in Cell B2 and then carries on and finds 86 so puts that is cell C2.

Hope thus is clear.

Thanks

 

[Trevor G]

See if these 2 formula will help.

In cell B2 = =IF(A1=VLOOKUP(Sheet2!$A1,Sheet2!$A$1:$A$14,1,0),LEFT(Sheet1!A1,2),"")
In cell C2 = =IF(A1=VLOOKUP(Sheet2!$A1,Sheet2!$A$1:$A$14,1,0),MID(Sheet1!A1,2,2),"")

 

[AndrewD04]

See if these 2 formula will help.

In cell B2 = =IF(A1=VLOOKUP(Sheet2!$A1,Sheet2!$A$1:$A$14,1,0),LEFT(Sheet1!A1,2),"")
In cell C2 = =IF(A1=VLOOKUP(Sheet2!$A1,Sheet2!$A$1:$A$14,1,0),MID(Sheet1!A1,2,2),"")

Hi,

Thank you this does not seem to work.

It just shows blank

 

[Trevor G]

Can you show some example data this will help find a solution.

 

[AndrewD04]

Sorry wont allow em to add the document - the To Search is the Raw data and will be a cell with multiple numbers and letters which all match against the number list.


To Search Result 1 Result 2 Result 3 Result 4
1928264B 19 28 26 4B


[TABLE="width: 96"]
<colgroup><col></colgroup><tbody>[TR]
[TD]Number Match[/TD]
[/TR]
[TR]
[TD]19[/TD]
[/TR]
[TR]
[TD]21[/TD]
[/TR]
[TR]
[TD]22[/TD]
[/TR]
[TR]
[TD]23[/TD]
[/TR]
[TR]
[TD]24[/TD]
[/TR]
[TR]
[TD]25[/TD]
[/TR]
[TR]
[TD]26[/TD]
[/TR]
[TR]
[TD]27[/TD]
[/TR]
[TR]
[TD]28[/TD]
[/TR]
[TR]
[TD]29[/TD]
[/TR]
[TR]
[TD]30[/TD]
[/TR]
[TR]
[TD]31[/TD]
[/TR]
</tbody>[/TABLE]

 

[Trevor G]

I am not getting this. Do all the numbers in the cell appear in the number list sheet? if so all you need is to split the numbers into seperate cells. The last part of the search is a letter, that wouldn't appear in a number list so it shouldn't appear in result 4 anyway from your example.

 

[AndrewD04]

I am not getting this. Do all the numbers in the cell appear in the number list sheet? if so all you need is to split the numbers into seperate cells. The last part of the search is a letter, that wouldn't appear in a number list so it shouldn't appear in result 4 anyway from your example.

Hi Trevor,

Thank you for trying to help me.

Basically I have a string of numbers and may have letters in it for example 25284B06 each number and number with letter so 25-28-4B-06 are in a list I have. I want excel to search the 25284B06 and when it finds a matching number such as 25 it puts that in a cell ad then carries on to find the next matching number which would be 28 and pit that in the next cell and so on