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

TVProducer

New Member
Joined
Dec 29, 2017
Messages
3
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
USER INPUT CELL
"B757"
"E120"
"B737"

<tbody>
</tbody>
"B190"
"A320"

<tbody>
</tbody>
"C650"

<tbody>
</tbody>

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!
 

Some videos you may like

Excel Facts

Using Function Arguments with nested formulas
If writing INDEX in Func. Arguments, type MATCH(. Use the mouse to click inside MATCH in the formula bar. Dialog switches to MATCH.

Norie

Well-known Member
Joined
Apr 28, 2004
Messages
76,271
Office Version
  1. 365
Platform
  1. Windows
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

New Member
Joined
Dec 29, 2017
Messages
3
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.
 

Norie

Well-known Member
Joined
Apr 28, 2004
Messages
76,271
Office Version
  1. 365
Platform
  1. Windows
I tried with your example data, without any quotes, and the formula I posted worked.
 

Watch MrExcel Video

Forum statistics

Threads
1,127,563
Messages
5,625,532
Members
416,116
Latest member
Joemamasuka

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top