Go Premium for a chance to win a PS4. Enter to Win

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 190
  • Last Modified:

If with Vlookup

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
Jagwarman
Asked:
Jagwarman
  • 5
  • 4
  • 2
1 Solution
 
Rob HensonIT & Database AssistantCommented:
=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
 
JagwarmanAuthor Commented:
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
 
Rgonzo1971Commented:
Hi,

pls try

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

Regards
0
Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

 
JagwarmanAuthor Commented:
Brilliant many thanks
0
 
Rob HensonIT & Database AssistantCommented:
@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
 
Rgonzo1971Commented:
Rob's right
I was carried away
0
 
Rob HensonIT & Database AssistantCommented:
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
 
JagwarmanAuthor Commented:
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
 
Rob HensonIT & Database AssistantCommented:
You will have to submit a Request for Attention to action that.

Thanks
Rob
0
 
JagwarmanAuthor Commented:
do you want me to do that Rob?
0
 
Rob HensonIT & Database AssistantCommented:
The moderators wont accept such a request from any one else.
0

Featured Post

[Webinar] Cloud and Mobile-First Strategy

Maybe you’ve fully adopted the cloud since the beginning. Or maybe you started with on-prem resources but are pursuing a “cloud and mobile first” strategy. Getting to that end state has its challenges. Discover how to build out a 100% cloud and mobile IT strategy in this webinar.

  • 5
  • 4
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now