VLOOKUP usbtracting VLOOKUP

[Figjam]

Hi All,

Trying to subtract a VLOOKUP from another VLOOKUP, I don't think I'm a million miles away but maybe VLOOKUP isn't the right choice, please can someone advise what is wrong with the formula below and if there is a better option?


=IF(ISNA(VLOOKUP(CONCATENATE(N$2,$A117),'DATA'!$AA$1:$AB$65536,2,0),0,(VLOOKUP(CONCATENATE(N$2,$A117),'DATA'!$AA$1:$AB$65536,2,0)))-(VLOOKUP(CONCATENATE(N$2,$A117),''DATA2'!$O:$P,2,0)),0,(VLOOKUP(CONCATENATE(N$2,$A117),'DATA2'!$O:$P,2,0))))

 

[TheCman81]

What version of excel are you using?

Does this work for you?

=IF(ISNA(VLOOKUP(CONCATENATE(N$2,$A117),DATA!$AA$1:$AB$65536,2,0)),0,(VLOOKUP(CONCATENATE(N$2,$A117),DATA!$AA$1:$AB$65536,2,0)))-IF(ISNA(VLOOKUP(CONCATENATE(N$2,$A117),DATA2!$O:$P,2,0)),0,(VLOOKUP(CONCATENATE(N$2,$A117),DATA2!$O:$P,2,0)))

IF you are using 2007+ you can use

=IFERROR(VLOOKUP(CONCATENATE(N$2,$A117),DATA!$AA$1:$AB$65536,2,0)-VLOOKUP(CONCATENATE(N$2,$A117),DATA2!$O:$P,2,0),0)

 

[Figjam]

Yes thanks, much appreciated