If and or

[Impreza13]

Hello! I've painted myself into a corner here with an IF/AND/OR statement. This is a generalization of what I'm trying to accomplish in Excel 2016. The column VEHICLE doesn't always contain the word "TRUCK" so it needs to be scrutinized. The following string causes Excel to tell me there is an error but I can't see where? I'm almost certain I'm overthinking this. Any assistance would be appreciated. Thanks!

=IF(AND(ISNUMBER(SEARCH("TRUCK",U2,OR(I2="GMC",I2="FORD")))),"USA",IF(AND(ISNUMBER(SEARCH("TRUCK",U2,OR(I2="HONDA","JAPAN","MISC"))))))

[TABLE="class: grid, width: 30"]
<tbody>[TR]
[TD="align: center"]VEHICLE
[/TD]
[TD="align: center"]MAKE
[/TD]
[TD="align: center"]RESULT[/TD]
[/TR]
[TR]
[TD]TRUCK[/TD]
[TD]GMC[/TD]
[TD]USA[/TD]
[/TR]
[TR]
[TD]TRUCK[/TD]
[TD]FORD[/TD]
[TD]USA[/TD]
[/TR]
[TR]
[TD]TRUCK[/TD]
[TD]HONDA[/TD]
[TD]JAPAN[/TD]
[/TR]
[TR]
[TD]TRUCK[/TD]
[TD]DIAHATSU[/TD]
[TD]MISC[/TD]
[/TR]
</tbody>[/TABLE]

 

[Gerald Higgins]

Hi, welcome to the board.

Sorry but I'm not clear what exactly you want the results to be.

What exactly is your input data ?
What exactly should the results be, and how should they be determined ?

 

[Marcelo Branco]

Maybe...


[TABLE="class: grid"]
<tbody>[TR]
[TD="bgcolor: #DCE6F1"][/TD]
[TD="bgcolor: #DCE6F1"]

A​

[/TD]
[TD="bgcolor: #DCE6F1"]

B​

[/TD]
[TD="bgcolor: #DCE6F1"]

C​

[/TD]
[TD="bgcolor: #DCE6F1"]

D​

[/TD]
[TD="bgcolor: #DCE6F1"]

E​

[/TD]
[TD="bgcolor: #DCE6F1"]

F​

[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

1​

[/TD]
[TD]

VEHICLE​

[/TD]
[TD]

MAKE​

[/TD]
[TD]

RESULT​

[/TD]
[TD][/TD]
[TD]

Lookup Table​

[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

2​

[/TD]
[TD]

TRUCK​

[/TD]
[TD]

GMC​

[/TD]
[TD]

USA​

[/TD]
[TD][/TD]
[TD="bgcolor: #D9D9D9"]

GMC​

[/TD]
[TD="bgcolor: #D9D9D9"]

USA​

[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

3​

[/TD]
[TD]

TRUCK​

[/TD]
[TD]

FORD​

[/TD]
[TD]

USA​

[/TD]
[TD][/TD]
[TD="bgcolor: #D9D9D9"]

FORD​

[/TD]
[TD="bgcolor: #D9D9D9"]

USA​

[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

4​

[/TD]
[TD]

TRUCK​

[/TD]
[TD]

HONDA​

[/TD]
[TD]

JAPAN​

[/TD]
[TD][/TD]
[TD="bgcolor: #D9D9D9"]

HONDA​

[/TD]
[TD="bgcolor: #D9D9D9"]

JAPAN​

[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

5​

[/TD]
[TD]

TRUCK​

[/TD]
[TD]

DIAHATSU​

[/TD]
[TD]

MISC​

[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

6​

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


Create a lookup table (gray area)

Formula in C2 copied down
=IF(ISNUMBER(SEARCH("TRUCK",A2)),IFERROR(VLOOKUP(B2,E$2:F$4,2,0),"MISC"),"")

M.

 

[Impreza13]

Ugh. I had a very long response crafted and apparently I took too long and was logged out and lost all of it. Booooo.

Thanks for the quick responses Gerald and Marcelo. Using Marcelo's clearer table example, if column A contains "TRUCK", then I want to make a determination of what to display in column C based on what is in column B. But I only care about specific entries in column B. If column A doesn't contain TRUCK then I don't care at all.

 

[Impreza13]

Marcelo, I mostly understand what you suggested and I'm going to do some research regarding what I don't understand about it. My entry-level understanding of VLOOKUP is that my lookup value is in column A, but in my live data example it is not. But it looks like you are working around that with the B2 statement with regards to the lookup table. I like it.

 

[Joe4]

Ugh. I had a very long response crafted and apparently I took too long and was logged out and lost all of it. Booooo.

If you are not on a public (shared) computer, if you click the "Remember Me" button when logging in, you won't get timed out.

 

[Marcelo Branco]

Marcelo, I mostly understand what you suggested and I'm going to do some research regarding what I don't understand about it. My entry-level understanding of VLOOKUP is that my lookup value is in column A, but in my live data example it is not. But it looks like you are working around that with the B2 statement with regards to the lookup table. I like it.

The formula is not complicated.
If TRUCK is found in column A then the formula searches the value in column B in lookup table's first column (E2:E4). If it is found the formula returns the correspondent value in column F; if not, the VLOOKUP generates an error and the function IFERROR returns the text "MISC".
If TRUCK is not found in column A the formula returns "" (empty string)
That's it.

M.

 

[Impreza13]

That worked perfectly, Marcelo. I truly appreciate yours and everyone else's help. Take care all!