IF-AND command
I have a numerical value in cell B1
If value in B1 < = 5 then display “8SWG”
If value in B1 > 5 but < 16 then display “12SWG”
If value in B1 >= 16 but < =150 then display “25x3mm”
If value in B1 >150 but < =200 then display “40x6mm”
If value in B1> =200 then display “50x6mm”
I tried with IF-AND, but it is returning the error “too many arguments”. Is there a command to do this?
Try this
Formula:
Please Login or Register to view this content.
If you like my answer please click on * Add Reputation
Don't forget to mark threads as "Solved" if your problem has been resolved
"Nothing is so firmly believed as what we least know."
--Michel de Montaigne
As an alternative to the above LOOKUP you could use a nested IF with the values in descending order and it will accomplish the same thing. There is no real need to use AND in this instance.
Formula:Please Login or Register to view this content.
Or, you could use a small table and use VLOOKUP.
BSB
Thanks Alkey. This works fine.
Thanks Mr. BadlySpelledBuoy. Could you please give an indication on how to implement using vlookup. I tried using vlookup also. I felt vlookup would do. But i was confused how to add conditions. Can you give a hint on how to do it
If you want to use VLOOKUP with lookup table
Enter formula in C1
Formula:Please Login or Register to view this content.
v B C D E 1 165 40x6mm 0 8SWG 2 6 12SWG 3 16 25x3mm 4 151 40x6mm 5 201 50x6mm
Just for information. If you want to use IF and < instead of > this will work
Formula:Please Login or Register to view this content.
You can use INDEX and MATCH instead of VLOOKUP
Formula:Please Login or Register to view this content.
8SWG 12SWG 25x3mm 40x6mm 50x6mm
<---------If you like someone's answer, click the star to the left of one of their posts to give them a reputation point for that answer.Ron W
Thanks once again Alkey for the demonstration of implementation using the command vlookup. It seems to be the professional approach to the problem. Learnt a new method of implementation.
Thanks once again. This forum is great.
Thanks Mr. newdoverman. INDEX and MATCH obviates the need for entering the output column required as arguments. Unfortunately, INDEX-MATCH is not used too often in excel lookups. Since the data space is less here, we may well go for this as suggested by you. Of course, it is much professional than IF-AND command. Works perfect. Thanks and keep going.
Last edited by melvinkoshy; 04-05-2016 at 09:26 PM.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks