One Cell References Multiple Cells - When False, Checks another set

[TVProducer]

Hello All,

So i have spent an hour researching this but it seems as though i cannot get the formula right.

My task is to take a reference cell (user input cell) and compare it with an array of data, when it matches something, it returns a value, else, it checks another array. see below example
[TABLE="width: 500"]
<tbody>[TR]
[TD]USER INPUT CELL
[/TD]
[TD]"B757"
[/TD]
[TD]"E120"
[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]

"B737"

<tbody>
</tbody>

[/TD]
[TD]"B190"
[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]

"A320"

<tbody>
</tbody>

[/TD]
[TD]"C650"
[/TD]
[/TR]
</tbody>[/TABLE]

so if someone enters into the user input cell B757 i want it to check the first array of values (2nd column), if it finds it return "Category D Aircraft", if not, it will check the next array, then return "Category E Aircraft", else "No Match."

The Formula i was using is IF(ISNA(MATCH(A1,B1:B3,0)),"CATEGORY D AIRCRAFT",IF(ISNA(MATCH(A1,C1:C3,0)),"CATEGORY E AIRCRAFT", "NO MATCH"))

Another thing that bugged me is that i have to put all values in quotes because they are strings. I want the user to not have to put anything in quotations when they put in an aircraft model.

Thanks for the help!

 

[Norie]

Perhaps.

=IF(ISNUMBER(MATCH(A1,B1:B3,0)),"CATEGORY D AIRCRAFT",IF(ISNUMBER(MATCH(A1,C1:C3,0)),"CATEGORY E AIRCRAFT", "NO MATCH"))

As for the quotes, why have you entered the values in B1:C3 in quotes in the first place?

You don't need to enter strings in quotes, you can just enter them directly in the cells.

 

[TVProducer]

Perhaps.

=IF(ISNUMBER(MATCH(A1,B1:B3,0)),"CATEGORY D AIRCRAFT",IF(ISNUMBER(MATCH(A1,C1:C3,0)),"CATEGORY E AIRCRAFT", "NO MATCH"))

As for the quotes, why have you entered the values in B1:C3 in quotes in the first place?

You don't need to enter strings in quotes, you can just enter them directly in the cells.

I added quotes because whenever i put in a value into user input, it wouldn't match. If i added quotations marks it would.

 

[TVProducer]

NEVERMIND - That seemed to work without quotes. Thank you

 

[Norie]

I tried with your example data, without any quotes, and the formula I posted worked.