I am trying to have VLOOKUP function only IF a cell is populated with any of 3 values. I'm using IF(OR(.... but that might not be the way to go. Any help would be great:
I also am adding 1 to the value returned from VLOOKUP:
=IF(OR(P2="1",P2="2",P2="3"),IF(ISNA(VLOOKUP(D2,QTR1_2011,14,FALSE)),"",(VLOOKUP(D2,QTR1_2011,14,FALSE)+1)))
Thanks to all you Experts.
Last edited by berger01; 01-27-2011 at 10:13 AM.
You don't have a FALSE action for your first IF, other than that you formula looks fine:
Please Login or Register to view this content.
Sorry, I didn't follow what you mean. Can you take another look at my formula and tell me exactly what I need. Thanks
=IF(OR(P2="1",P2="2",P2="3"),IF(ISNA(VLOOKUP(D2,QTR1_2011,14,FALSE)),"",(VLOOKUP(D2,QTR1_2011,14,FALSE)+1)))
What mdbct means is you need to add a "what-if-false" argument to your first IF in case P1 doesn't equal any of your conditions... but also remember our previous threads together... you are wrapping numbers in quotes... are you sure you want that?
Try:
=IF(OR(P2=1,P2=2,P2=3),IF(ISNA(VLOOKUP(D2,QTR1_2011,14,FALSE)),"",(VLOOKUP(D2,QTR1_2011,14,FALSE)+1)),"")
Where there is a will there are many ways.
If you are happy with the results, please add to the contributor's reputation by clicking the reputation icon (star icon) below left corner
Please also mark the thread as Solved once it is solved. Check the FAQ's to see how.
Good Morning NBVC.
The change you gave me worked... Thank you. I have a bad habit of enclosing in ""s.
Well now I am questioning my own structure:
I'm trying to count "create an increment" by adding what was on the previous worksheet (If the same record exists) +1. But only if numbers 1,2,3 are in P2 of the current worksheet. Right now it adds 1 even if VLOOKUP did not find a number in the previous.
If VLOOKUP does not find the record on the previous, then it should return "".... and ignore the +1
Attached is the worksheet. The formula we are working on is in Q2 of the 2nd sheet "2011 2nd Qtr". VLOOKUP is returning Q2 from the 1st.
Thank you again.
That is because Vlookup found a match and the corresponding cell in the 14th column is sometimes blank, but Vlookup returns 0 when that is the case... and adds the 1...
Try:
Please Login or Register to view this content.
That did it! Again Thanks so much.
You probably have a long list of (I owe you) lunches, so I'll offer a dinner.
While you have the attachement, notice the errors in the blank cells in columns O, P, Q down around rows 190. They disappear when data is populated..... is there a way I can get rid of those? It tells me those cells are the wrong data type, but I can't seem to find a fix. Its not a deal breaker, but would clean things up alittle.
Thanks NBVC, you have a great day.
I don't see any errors or warnings....
Usually if you see those, it warns you that you have numbers formatted as text and you might want to fix, or formulas in adjacent cells are slightly different, or other warnings.. usually, you can right-click and ignore if you know you are good.
Got it, thanks
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks