checking cell 'tesxt' value

[mwvirk]

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")

 

[MrKowz]

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]

So this is what I did. I made a list of the fruits in another tab, named it Fruitlist and used this formula:

=IF((MATCH(A1,Fruitlist)=5),"Fruit","Veg")

 

[T. Valko]

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]

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]

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]

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]

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]

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]

I sent you a PM back regarding that issue. The problem is that the first array must be in alphabetical order.