If and or

Impreza13

New Member
Joined
Nov 28, 2017
Messages
5
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!

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


VEHICLE
MAKE
RESULT
TRUCKGMCUSA
TRUCKFORDUSA
TRUCKHONDAJAPAN
TRUCKDIAHATSUMISC

<tbody>
</tbody>
 

Excel Facts

Enter current date or time
Ctrl+: enters current time. Ctrl+; enters current date. Use Ctrl+: Ctrl+; Enter for current date & time.
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 ?
 
Upvote 0
Maybe...


A
B
C
D
E
F
1
VEHICLE​
MAKE​
RESULT​
Lookup Table​
2
TRUCK​
GMC​
USA​
GMC​
USA​
3
TRUCK​
FORD​
USA​
FORD​
USA​
4
TRUCK​
HONDA​
JAPAN​
HONDA​
JAPAN​
5
TRUCK​
DIAHATSU​
MISC​
6

<tbody>
</tbody>


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.
 
Upvote 0
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.
 
Upvote 0
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.
 
Upvote 0
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.
 
Last edited:
Upvote 0
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.
 
Last edited:
Upvote 0
That worked perfectly, Marcelo. I truly appreciate yours and everyone else's help. Take care all!
 
Upvote 0

Forum statistics

Threads
1,213,526
Messages
6,114,122
Members
448,550
Latest member
CAT RG

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
Back
Top