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
JagwarmanAsked:
Who is Participating?
 
Rgonzo1971Connect With a Mentor Commented:
Hi,

pls try

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

Regards
0
 
Rob HensonFinance AnalystCommented:
=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
Get your problem seen by more experts

Be seen. Boost your question’s priority for more expert views and faster solutions

 
JagwarmanAuthor Commented:
Brilliant many thanks
0
 
Rob HensonFinance AnalystCommented:
@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 HensonFinance AnalystCommented:
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 HensonFinance AnalystCommented:
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 HensonFinance AnalystCommented:
The moderators wont accept such a request from any one else.
0
All Courses

From novice to tech pro — start learning today.