A little Excel help please

[Colbaker]

Hi all,

I'm struggling a bit with a formula and i am sure you genius's will be able to help me .

What i am trying to do is a forulma that it 2 critera match from 2 different tables then a YES will show and if not a NO will show.

So for instance, If CELL B1 has the word BMW in it and also has one of the words that are in the TABLE named BMW, so for this instance 3 Series then i want the formula Cell to state YES, if not then the cell needs to state NO.

What do you think, is this simple and i am missing something obvious?

 

[HeX]

So, from reading that I am assuming you have two columns. One for the make of car, and one for the model, and you want to make sure that the model is contained within the table that corresponds to the make. If it does flag it as valid with a YES, if not flag it as a NO.

On here we have two lists of valid car models. Each is a named range. The list of BMW models is called BMW, and the list of Toyota models is called... Toyota.

The Valid column has the formula:

=IF(COUNTIF(INDIRECT($A4),$B4),"Yes","No")

What this does is count the number of times the 'Type' (B4) appears in the named range (A4), if it's in the list and return Yes, otherwise return No.


A better way of doing this though would be to use named ranges to give linked data validation (in the form of a drop down list). So say if someone selects BMW in the first box then the Type box would be a list of valid BMW entries and it wouldn't let you type in anything else. This would save you having to do manual data validation.