|
Author
|
Topic: If Statement Help
|
Quincy
Single Celled Newbie
Member # 39479
Rate Member
|
posted June 15, 2011 10:41
I have the following formula in cell D2: =Vlookup($C2,Indirect($A2),3,0) and it works fine. However because of a special case I need to check cell B2 input and added the following if statement: =IF($B2<>"L",Vlookup($C2,Indirect($A2),3,0),"Text String"). Unfortunately, I do not get the desired result. If B2=L, I get the text string; however, if I enter A or B (the other two choices) I get #N/A. I don't understand, and wonder if you can shed some light on the apparent problem.
Quinc
Posts: 3 | From: Utah | Registered: Jun 2011
| IP: Logged
|
|
|
|
Quincy
Single Celled Newbie
Member # 39479
Rate Member
|
posted June 15, 2011 14:47
Thank you, Grumpy, for your reply. I'm not sure it is helpful, however. The VLOOKUP function worked prior to adding the IF statement. The IF statement seems to be properly formed, but when I added it, the VLOOKUP function gave the #NA result. I'm trying to determine what happened when I added the IF statement. Is there a problem with it, and if not how did it change the VLOOKUP function?
Posts: 3 | From: Utah | Registered: Jun 2011
| IP: Logged
|
|
dragonman97
SuperFan!
Member # 780
Member Rated:
|
posted June 15, 2011 16:14
Debugging 102: Break your problem into small problems.
Put almost each part of your formula into a different cell, and chain them.
Once you understand how Excel is evaluating each statement, you may figure out that something isn't actually correct...and/or that you need to use another formula to get the desired results.
--------------------
There are three things you can be sure of in life: Death, taxes, and reading about fake illnesses online...
Posts: 9039 | From: Westchester County, New York | Registered: May 2001
| IP: Logged
|
|
The Famous Druid
Gold Hearted SuperFan!
Member # 1769
Member Rated:
|
posted June 15, 2011 18:14
quote:
Originally posted by dragonman97:
Debugging 102: Break your problem into small problems.
Sounds like good advice.
/me grabs machete
/me wanders off in search of a certain user...
--------------------
If you watch 'The History Of NASA' backwards, it's about a space agency that has no manned spaceflight capability, then does low-orbit flights, then lands on the Moon.
Posts: 10313 | From: Melbourne, Australia | Registered: Oct 2002
| IP: Logged
|
|
Ugh, MightyClub
BlabberMouth, the Next Generation
Member # 3112
Member Rated:
|
posted June 16, 2011 06:29
VLOOKUP() will return #N/A if the lookup value ($C2 in your case) is either blank or is not found in the lookup table (INDIRECT($A2) in your example).
Try putting just the VLOOKUP piece of the formula in another column of the same row and see if you get the expected result in that column but *not* the column with the IF() version.
There is nothing I've ever heard of that would make VLOOKUP() (or any other function) behave differently when embedded in an IF().
In fact, this page suggests putting VLOOKUP() in an IF() construct to hide the #N/A when it happens: http://pubs.logicalexpressions.com/pub0009/LPMArticle.asp?ID=446
--------------------
Ugh!
Posts: 1613 | From: Ithaca, NY | Registered: Dec 2004
| IP: Logged
|
|
Quincy
Single Celled Newbie
Member # 39479
Rate Member
|
posted June 16, 2011 17:12
Thank you for your replies. I discovered that the problem was I data validated (referenced) a table on a separate worksheet. Moving it solved my problems. Again, Thank you.
Posts: 3 | From: Utah | Registered: Jun 2011
| IP: Logged
|
|
|
UBBFriend: Email this page to someone!
Printer-friendly view of this topic
