Lookup function - How to select 2 or more results?

[Fletch74]

Hi,

I've had some help from some guru's on here in the past few days But I still have a couple of more issues I'm working through in this database download.

I have attached an example of what I am trying to do. The first column is listing invoices, and sometimes I have a "Line Feed" in the one cell, this can be sorted by using the formula I have in the 2nd column =SUBSTITUTE(A2,CHAR(10),""), however this causes an issue in the 3rd column in which I am trying to extract the Vehicle franchises of Mitsubishi, Kia, Ford, etc using a SEARCH formula from a list as the Line Feed is still in the text. Is there a way I can incorporate the Line Feed into the Search formula?

Also, on a side note, if there are multiple franchises (ie. Mitsubishi & Kia) as per the first line, how do I get the search to display both of them, and not just the first one?

Cheers for the help

Fletch

[Fletch74]

Can anyone help??

I've managed to fix the Line Feed issue with the REPLACE formula identifying the character and replacing it, but I still don't know how to extract multiple frnachises for my list of franchisies, unless I include all the combinations in my list I am looking up, which I don't want to do.

Thanks,

[azumi]

and where is the lookup table? in your workbook just table for retrieve data?

Azumi

[Fletch74]

It's in another worksheet. This is a sample for this site.

Below is the table;

Kia
Ford
Suzuki
Holden
Hyundai
Mitsubishi
Honda
Mercedes
Land Rover
Jaguar
Toyota
Subaru
Nissan

The problem is when there is more than one of those in a cell, how do I show all the franchises that are listed from that cel, rather than just the first.

Thanks,

[Fletch74]

I have updated the spreadsheet to include the table.

Any help would be much appreciated.

[azumi]

=MID(B2,FIND(":",B2,1),FIND(":",B2,1))

Azumi

[Fletch74]

Cheers Azumi.

I'll add it into the other Formula in Column C, and create an IF formula.