Vlookup with multiple IF Statements

[lsab]

Hi all,

I've got the following formula...
=IF(ISTEXT(M6),VLOOKUP(M6,ContractHire!$B$2:$AE$999999,17,FALSE)," ")

However I need to add some further conditions.
H1 = 48
J1 = 10000

I need these conditions to match those in the external workbook.
In simple terms - find line which matches M6, H1 & J1.

Is there also a more elegant end efficient than what I already have?

Thanks in advance.


Lewis

 

[bclaredal]

Use an AND statement in your logical_test if you want to return something that matches all 3 cells

 

[lsab]

Thanks for your reply.
Could you please give me an example of how that may look?

 

[bclaredal]

IF(AND(ISTEXT(M6),<Enter second argument for H1>, <Enter third argument for J1>),VLOOKUP(M6,ContractHire!$B:$AE,17,FALSE),"")

 

[lsab]

I just get #VALUE! returned.
=IF(AND(ISTEXT(M6),, ),VLOOKUP(M6,ContractHire!$B:$AE,17,FALSE),"")*IF(AND(ISTEXT(N6),,),VLOOKUP(N6,ContractHire!$B:$AE,12,FALSE),"")

Is that correct?

Thanks

 

[lsab]

I managed to resolve this using.....

=INDEX(ContractHire!$R:$R,MATCH(1,(ContractHire!$B:$B=$M6)*(ContractHire!$N:$N=$O6)*(ContractHire!$M:$M=$N6),0))
Applied with CTRL + Shift + Enter

Thanks for your help