Solved

If with Vlookup

Posted on 2014-09-09
11
179 Views
Last Modified: 2014-09-11
Can an Expert solve this for me please.

I need a formula in C4 to say:

If H4 is Blank then C4 is blank. If J4 or K4 = B12345 Vlookup H4,Client,2,0 IF K4 = C12345 Vlookup H4 House,2,0

Thank you in advance
0
Comment
Question by:Jagwarman
  • 5
  • 4
  • 2
11 Comments
 
LVL 31

Expert Comment

by:Rob Henson
ID: 40312318
=IF(H4="","",IF(OR(J4="B12345",K4="B12345"),VLOOKUP(H4,Client,2,0),IF(K4="C12345",VLOOKUP(H4,House,2,0))))

I have assumed B12345 and C12345 are cell values rather than cell references. If cell references, just remove the double quotes around those values.

Are there other scenarios that need to be considered?

Thanks
Rob H
0
 

Author Comment

by:Jagwarman
ID: 40312345
I made a mistake Rob

IF K4 = C12345 Vlookup H4 House,2,0


should read

IF J4 OR K4 = C12345 Vlookup H4 House,2,0
0
 
LVL 48

Accepted Solution

by:
Rgonzo1971 earned 500 total points
ID: 40312352
Hi,

pls try

=IF(H4="","",IF(OR(J4="B12345",K4="B12345",J4="C12345",K4="C12345"),VLOOKUP(H4,Client,2,0),""))

Regards
0
 

Author Closing Comment

by:Jagwarman
ID: 40312372
Brilliant many thanks
0
 
LVL 31

Expert Comment

by:Rob Henson
ID: 40312394
@jagwarman - the solution that you have accepted only looks at Client. Do you not need it to look at House in the second scenario?

If so:

=IF(H4="","",IF(OR(J4="B12345",K4="B12345"),VLOOKUP(H4,Client,2,0),IF(OR(J4="C12345",K4="C12345"),VLOOKUP(H4,House,2,0))))
0
IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

 
LVL 48

Expert Comment

by:Rgonzo1971
ID: 40312435
Rob's right
I was carried away
0
 
LVL 31

Expert Comment

by:Rob Henson
ID: 40314294
Another shorter option, assuming only two options B12345 or C12345:

=IF(H4="","",VLOOKUP(H4,IF(OR(J4="B12345",K4="B12345"),Client,House),2,0))

If there are other options for J4 & K4:

=IF(H4="","",VLOOKUP(H4,IF(OR(J4="B12345",K4="B12345"),Client,IF(OR(J4="C12345",K4="C12345"),House)),2,0))

This allows for the second option (C12345), are there others? This allows for only B12345 and C12345, other values in J4 or K4 will give an error. Is there a common theme for each, such as these begin with B or C. If there is a common theme, it can probably be shortened even further with a helper table.

Thanks
Rob H
0
 

Author Comment

by:Jagwarman
ID: 40314506
Rob you are right I meant to accept your solution. it must have been late in the day. Sorry and thanks for the alternatives

Rgonzo, give Rob the points :-)
0
 
LVL 31

Expert Comment

by:Rob Henson
ID: 40314602
You will have to submit a Request for Attention to action that.

Thanks
Rob
0
 

Author Comment

by:Jagwarman
ID: 40316933
do you want me to do that Rob?
0
 
LVL 31

Expert Comment

by:Rob Henson
ID: 40317041
The moderators wont accept such a request from any one else.
0

Featured Post

Why You Should Analyze Threat Actor TTPs

After years of analyzing threat actor behavior, it’s become clear that at any given time there are specific tactics, techniques, and procedures (TTPs) that are particularly prevalent. By analyzing and understanding these TTPs, you can dramatically enhance your security program.

Join & Write a Comment

Drop Down List with Unique/Distinct Values (Part II - ComboBox or ListBox and Data Validation List Bonus!) David Miller (dlmille) Intro This article focuses on delivering unique, sorted lists to list objects (e.g., ComboBox, ListBox) and Dat…
This article descibes how to create a connection between Excel and SAP and how to move data from Excel to SAP or the other way around.
Viewers will learn the basics of slicers and timelines for both PivotTables and standard Excel tables in Excel 2013.
This Micro Tutorial will demonstrate how to create pivot charts out of a data set. I also added a drop-down menu which allows to choose from different categories in the data set and the chart will automatically update.

747 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

9 Experts available now in Live!

Get 1:1 Help Now