Excel-which formula?

  • Thread starter Thread starter krù
  • Start date Start date
K

krù

Hello everybody!

I have a table in which I have 2 columns, named 'COMPANIES' and 'MODEL'
and 8 rows with the models for each company, that of course are 8.

I also have a list in another worksheet called LC in which models are
shown in columns.

Now, which formula should I use to make excel do this:

"choose only the values of the list B50:B81 in worksheet LC that are
included in the table A1:P9 if I indicate the company name".

The result should be a new list in which there are only the models of
the company I have selected through a combo box.

Is there someone who can help me?

Thank you

Krù
 
Not really sure, but here goes one pitch ..

In sheet: Table
you have listed in A1:B9

Co Models
Co1 Mod1
Co2 Mod2
Co3 Mod3
Co4 Mod4
Co5 Mod5
Co6 Mod6
Co7 Mod7
Co8 Mod8

(above list assumed to consist of unique items only)

In sheet: LC
you have a list of models in B50:B81, example:

Mod8
Mod2
Mod4
etc

In another sheet: Sheet1 (say)
you have in listed in A1 down, the companies

Co8
Co3
Co1
etc

Try in B1:

=IF(ISNA(MATCH(OFFSET(Table!$A$1,MATCH(A1,Table!A:A,0)-1,1),LC!$B$50:$B$81,0
)),"",OFFSET(Table!$A$1,MATCH(A1,Table!A:A,0)-1,1))

Copy B1 down

Col B will return the models from "Table" corresponding to the company in
col A
but only if the models appear in the list in B50:B81 in "LC",
otherwise "blanks", i.e. "" will be returned
 
Back
Top