Need help in Excel Formula

Question

Hi,&nbsp;Suppose I have two hundred thousend rows. Each row has car model entries. I would like to write a formula in column B to determine only the model column A belongs to. The formula will just lookup up the exact model in column A from&nbsp;the&nbsp;list of all models available.&nbsp;e.g:&nbsp;Column A&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; Column BFocus&nbsp;1.5G GAS A&nbsp; &nbsp; &nbsp; &nbsp; &nbsp;FocusFocus&nbsp;1.3E GAS M&nbsp; &nbsp; &nbsp; &nbsp; &nbsp;FocusGolf 1.3 GAS M&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;GolfGolf1.5 GAS M&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;&nbsp;GolfGolf2.0&nbsp;GAS AT&nbsp;&nbsp;&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;GolfX3xdrive201&nbsp;BMW&nbsp; &nbsp; &nbsp; &nbsp; X3&nbsp;

lorenzo kim · Answer

as I see it, you want column B to get the left string letters only?if it is separated by a space or comma (delimiter) - it would be quite easy - butX3xdrive201 BMW - this would be a problem....

eric we™ · Answer

Thanks for the reply. No i dont need to extract the left string only. The model name is randomly positioned. It could be in the middle or right. Thats why i need a search function?

lorenzo kim · Answer

would you kindly attach a non sensitive file for the people here to better understand your need.thanks..

sergeibaklan · Answer

If list of models is in column E as here

that could be array formula (Ctrl+Shift+Enter)
=INDEX($E$1:$E$3,MATCH(1,--ISNUMBER(SEARCH($E$1:$E$3,A1)),0))
and attached

Forum Discussion

Need help in Excel Formula

4 Replies

Resources