checking cell 'tesxt' value

mwvirk

Board Regular
Joined
Mar 2, 2011
Messages
156
i'm using drop down menu in cell a1 where list of some items is created like:
Orange
Apple
Banana
Onion
Carrot
Grapes

in cell b1 i want to check if a1 is Onion or Carrot then it should give value in b1 "Veg" and if cell a1 is Orange or Apple or Banana or Grapes then b1 should have value "Fruit"

please help me to correct my formula:
=IF(ISNUMBER(FIND("Orange","Apple","Banana","Onion","Carrot","Grapes"",A1)),"Veg","Fruit")
 

Excel Facts

Workdays for a market open Mon, Wed, Friday?
Yes! Use "0101011" for the weekend argument in NETWORKDAYS.INTL or WORKDAY.INTL. The 7 digits start on Monday. 1 means it is a weekend.

MrKowz

Well-known Member
Joined
Jun 30, 2008
Messages
6,653
Office Version
  1. 365
  2. 2016
Platform
  1. Windows
Try a LOOKUP formula (note, the first defined array must be in alphabetical order):

Excel 2003<TABLE style="BORDER-RIGHT: #a6aab6 1px solid; BORDER-TOP: #a6aab6 1px solid; BORDER-LEFT: #a6aab6 1px solid; BORDER-BOTTOM: #a6aab6 1px solid; BORDER-COLLAPSE: collapse; BACKGROUND-COLOR: #ffffff" cellPadding=2 rules=all><COLGROUP><COL style="BACKGROUND-COLOR: #e0e0f0" width=25><COL><COL></COLGROUP><THEAD><TR style="COLOR: #161120; BACKGROUND-COLOR: #e0e0f0; TEXT-ALIGN: center"><TH></TH><TH>A</TH><TH>B</TH></TR></THEAD><TBODY><TR><TD style="COLOR: #161120; TEXT-ALIGN: center">1</TD><TD>Carrot</TD><TD>Veg</TD></TR></TBODY></TABLE><TABLE style="BORDER-RIGHT: #a6aab6 1px solid; BORDER-TOP: #a6aab6 1px solid; BORDER-LEFT: #a6aab6 1px solid; BORDER-BOTTOM: #a6aab6 1px solid; BORDER-COLLAPSE: collapse; BACKGROUND-COLOR: #ffffff" cellPadding=2 rules=all><THEAD><TR style="COLOR: #161120; BACKGROUND-COLOR: #e0e0f0; TEXT-ALIGN: center"><TH>Sheet1</TH></TR></TD></THEAD><TBODY></TBODY></TABLE>

<TABLE style="BORDER-RIGHT: black 2px solid; PADDING-RIGHT: 0.4em; BORDER-TOP: black 2px solid; PADDING-LEFT: 0.4em; PADDING-BOTTOM: 0.4em; BORDER-LEFT: black 2px solid; PADDING-TOP: 0.4em; BORDER-BOTTOM: black 2px solid; BORDER-COLLAPSE: collapse; BACKGROUND-COLOR: #ffffff" cellPadding=2 rules=all width="85%"><TBODY><TR><TD style="PADDING-RIGHT: 6px; PADDING-LEFT: 6px; PADDING-BOTTOM: 6px; PADDING-TOP: 6px">Worksheet Formulas<TABLE style="BORDER-RIGHT: #a6aab6 1px solid; BORDER-TOP: #a6aab6 1px solid; BORDER-LEFT: #a6aab6 1px solid; BORDER-BOTTOM: #a6aab6 1px solid; BORDER-COLLAPSE: collapse; BACKGROUND-COLOR: #ffffff; TEXT-ALIGN: center" cellPadding=2 rules=all width="100%"><THEAD><TR style="COLOR: #161120; BACKGROUND-COLOR: #e0e0f0"><TH width=10>Cell</TH><TH style="PADDING-LEFT: 5px; TEXT-ALIGN: left">Formula</TH></TR></THEAD><TBODY><TR><TH style="COLOR: #161120; BACKGROUND-COLOR: #e0e0f0" width=10>B1</TH><TD style="TEXT-ALIGN: left">=LOOKUP(A1,{"Apple","Banana","Carrot","Grapes","Onion","Orange"},{"Fruit","Fruit","Veg","Fruit","Veg","Fruit"})</TD></TR></TBODY></TABLE></TD></TR></TBODY></TABLE>
 

jmthompson

Well-known Member
Joined
Mar 31, 2008
Messages
966
So this is what I did. I made a list of the fruits in another tab, named it Fruitlist and used this formula:
Code:
=IF((MATCH(A1,Fruitlist)=5),"Fruit","Veg")
 

T. Valko

Well-known Member
Joined
May 9, 2009
Messages
16,623
i'm using drop down menu in cell a1 where list of some items is created like:
Orange
Apple
Banana
Onion
Carrot
Grapes

in cell b1 i want to check if a1 is Onion or Carrot then it should give value in b1 "Veg" and if cell a1 is Orange or Apple or Banana or Grapes then b1 should have value "Fruit"

please help me to correct my formula:
=IF(ISNUMBER(FIND("Orange","Apple","Banana","Onion","Carrot","Grapes"",A1)),"Veg","Fruit")
Create a 2 column table like this:

Orange Fruit
Apple Fruit
Banana Fruit
Onion Veg
Carrot Veg
Grapes Fruit

Let's assume that table is in the range G1:H6.

Then:

=IF(A1="","",VLOOKUP(A1,G1:H6,2,0))
 

mwvirk

Board Regular
Joined
Mar 2, 2011
Messages
156

ADVERTISEMENT

thnx for all ur suggestion. actually i'm looking for something without adding any other tab or sheet. is it possible to use the same cells the 1 i'm using to create dropdown menu.
i do have a column where all items are mentioned and i'm using that column to get my dropdown menu. but i cannot add any other tab or sheet.
pls advise.
 

MrKowz

Well-known Member
Joined
Jun 30, 2008
Messages
6,653
Office Version
  1. 365
  2. 2016
Platform
  1. Windows
thnx for all ur suggestion. actually i'm looking for something without adding any other tab or sheet. is it possible to use the same cells the 1 i'm using to create dropdown menu.
i do have a column where all items are mentioned and i'm using that column to get my dropdown menu. but i cannot add any other tab or sheet.
pls advise.

Check my post (post #2) - it hardcodes the values inside the formula.
 

mwvirk

Board Regular
Joined
Mar 2, 2011
Messages
156

ADVERTISEMENT

ok. done. thnx MrKowz - i'm using your tech. thnx #Nmae? thats was also nice idea.
now please help me again here:
=IF(ISBLANK(M4),”Pending”,””),(isblank(u4),"Resolved","")

M4 is by default blank but i'm checking if M4 contain any value than in W4 it should give me Pending - but once i enter something in U4 then W4 should convert to Resolved
help needed again :(
 

T. Valko

Well-known Member
Joined
May 9, 2009
Messages
16,623
now please help me again here:
=IF(ISBLANK(M4),”Pending”,””),(isblank(u4),"Resolved","")

M4 is by default blank but i'm checking if M4 contain any value than in W4 it should give me Pending - but once i enter something in U4 then W4 should convert to Resolved
help needed again :(
Maybe this...

=IF(AND(M4<>"",U4<>""),"Resolved",IF(M4<>"","Pending",""))
 

mwvirk

Board Regular
Joined
Mar 2, 2011
Messages
156
according to below formula, Receipt Printer should be "Partial" but its giving me output as "Full"

=lookup(i4,{"BNA","Card Reader","Cash Handler","Dispenser","Receipt Printer","Journal Printer","Capturing Cards","Out Of Cash","Low On Cash","No Transactions","Wrong value","Negative Balance","Pick Failure","Communication","Display","Hardware","Software","DV-RW","Camera"},{"Partial","Full","Full","Partial","Partial","Full","Full","Full","Partial","Full","Partial","Partial","Par tial","Full","Full","Full","Full","Partial","Partial"})
 

MrKowz

Well-known Member
Joined
Jun 30, 2008
Messages
6,653
Office Version
  1. 365
  2. 2016
Platform
  1. Windows
I sent you a PM back regarding that issue. The problem is that the first array must be in alphabetical order.
 

Forum statistics

Threads
1,141,843
Messages
5,708,922
Members
421,598
Latest member
NewHere

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