Solved

potential if/v lookup formula error

Posted on 2014-02-17
8
192 Views
Last Modified: 2014-02-17
Hi Expert's Excel 2007

I am using the following formula to complete the following scenario. .
Formula:-
=IF(o5="",IF(VLOOKUP(Sheet2!B2,Sheet2!$C$2:$Q$500,14,0)="",VLOOKUP(Sheet2!B2,Sheet2!$C$2:$Q$500,10,0),VLOOKUP(Sheet2!B2,Sheet2!$C$2:$Q$500,14,0)),"")

Scenario: sheet 1

If cell o5 is blank and sheet 2 col 14 is blank then look up col 12. If o5 is blank and col 12 is blank then look up col 14...sheet 2
0
Comment
Question by:route217
  • 5
  • 2
8 Comments
 
LVL 8

Expert Comment

by:itjockey
ID: 39864530
pls provide sample file...
0
 
LVL 8

Expert Comment

by:itjockey
ID: 39864533
=IF(AND(O5="",IF(VLOOKUP(Sheet2!B2,Sheet2!$C$2:$Q$500,14,0)=""),VLOOKUP(Sheet2!B2,Sheet2!$C$2:$Q$500,10,0),VLOOKUP(Sheet2!B2,Sheet2!$C$2:$Q$500,14,0)),"")
0
 
LVL 49

Expert Comment

by:Rgonzo1971
ID: 39864536
Hi,

Yu speak of col 12 and 14 but in your formula you have 10 and 14

Are the col numbers relative to c or relative to a

Regards
0
 

Author Comment

by:route217
ID: 39864637
Hi Rgonzo

Lets stick to the col in the formula. ...

I am getting an error with it jockeys formula after =""), part..
0
Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

 
LVL 8

Expert Comment

by:itjockey
ID: 39864762
Try this.  =IF(AND(O5="",IF(VLOOKUP(Sheet2!B2,Sheet2!$C$2:$Q$500,14,0)=""),VLOOKUP(Sheet2!B2,Sheet2!$C$2:$Q$500,10,0),VLOOKUP(Sheet2!B2,Sheet2!$C$2:$Q$500,14,0)),""

allows to auto complete
0
 
LVL 8

Expert Comment

by:itjockey
ID: 39864766
As I am not in position to investigate right now.Traveling.

Thanks
0
 
LVL 8

Accepted Solution

by:
itjockey earned 500 total points
ID: 39864785
=IFERROR(IF(AND(O5="",VLOOKUP(Sheet2!B2,Sheet2!$C$2:$Q$500,14,0)=""),VLOOKUP(Sheet2!B2,Sheet2!$C$2:$Q$500,10,0),VLOOKUP(Sheet2!B2,Sheet2!$C$2:$Q$500,14,0),"")
0
 

Author Comment

by:route217
ID: 39864842
Thanks for the feedback.. works
0

Featured Post

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Drop Down List with Unique/Distinct Values (enhancing the Combo-Box with a few steps and a little code) David miller (dlmille) Intro Have you ever created a data validation list from a database field or spreadsheet column (e.g., Zip Codes or Co…
Freeze panes is an option within all variants of Excel to enable parts of a sheet to remain stationary when the cursor is in another part of the sheet. This is a very useful feature which is overlooked or under used.
The viewer will learn how to create two correlated normally distributed random variables in Excel, use a normal distribution to simulate the return on different levels of investment in each of the two funds over a period of ten years, and, create a …
Graphs within dashboards are meant to be dynamic, representing data from a period of time that will change each time the dashboard is updated with new data. Rather than update each graph to point to a different set within a static set of data, t…

863 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

19 Experts available now in Live!

Get 1:1 Help Now