IF formula

[Peter]

Hi All

I am looking for a formula that does not used fixed cells to give me the
result

Formula that works at the moment is =if(B20=Terms!A2,Invoices!H20+Terms!B2)

Contents in B20 and H20 will change each month so can not use the formula -
I have tried using =if(b:b=Terms!A:A,Invoices!h:h+terms!b:b) but returns FALSE

i.e.

Invoices Terms

Col B Col H Col A Col B
Bov007 31/12/08 Bov007 45

Formula should give
14/2/08

Any suggestions would be grealty appreciated

 

[Bernie Deitrick]

=H20 + VLOOKUP(B20,Terms!A:B,2,False)

HTH,
Bernie
MS Excel MVP

 

[Peter]

Hi Bernie

Thanks for that - a great help (not very good at the lookups) -

Next thing and i'm sure it's probably straightforward is that if False then
=h20+30

 

[Bernie Deitrick]

Peter,

This will use 30 is the item is not found in the terms list:

=H20 + IF(ISERROR(VLOOKUP(B20,Terms!A:B,2,False)),30,VLOOKUP(B20,Terms!A:B,2,False))

HTH,
Bernie
MS Excel MVP

 

[Peter]

Supberb - thanks for your help and promptness Bernie.

 

[Bernie Deitrick]

Supberb - thanks for your help and promptness Bernie.

You're quite welcome.

Bernie