Searching for a value in part of a column

[Sophie.Durrant]

I am trying to find out if there is a function to search for a value within a cell within a column.

I have a sheet containing customer information and need to look up into a different sheet containing sales data. The problem I have is that on the first sheet the customer number and name are in separate cells but in the second sheet they are together in one cell with the customer number in brackets following the name. The customer number is always the same, but sometimes the name can be spelled differently one one sheet than it is on the other so I need to use the customer number as my identifying value and not the name.

I have tried using concatenate in the first sheet to get the customer name and number together and that found a lot with a Vlookup formula, but as some of the names are spelled differently in the second sheet I am now faced with manually searching for the number in the second sheet and copying and pasting the name into the first sheet so the lookup works, there are thousands of rows andI need this sheet done by tomorrow!

Has anybody got any suggestions please? I hate it when I am defeated by Excel!

Sorry for the ramling question

[scottylad2]

For Clarity, are you looking to sum or count the sales data or are you looking to list all data rows relating to that customer number? can you upload a small sample?

[Kyle123]

Hi Sophie,

Can you post a sample workbook with the data laid out in the same format - but with sensitive data taken out?

Easier for me to have a look at then

Go Advanced > Manage Attachements

[martindwilson]

extract the numbers into another column on sheet 2 then use index match to pull the data back to sheet 1
like this