Here's a really ugly, inefficient formula which does what you asked:

=IF(ISERROR(VLOOKUP(Blank!$B$18,Miles!$B$3:$D$16,3 ,FALSE)),0,IF(ISERROR(VLOOKUP(Blank!$C$18,Miles!$C $3:$D$16,2,FALSE)),0,IF(VLOOKUP(Blank!$B$18,Miles! $B$3:$D$16,3,FALSE)=VLOOKUP(Blank!$C$18,Miles!$C$3 :$D$16,2,FALSE),VLOOKUP(Blank!$C$18,Miles!$C$3:$D$ 16,2,FALSE),0)))

And here's an easier method:

1. On the MILES sheet, enter this formula in the first free column (I'll
assume it's column E):

=IF(AND(Miles!B3=Blank!B18,Miles!C3=Blank!C18),Mil es!D3,0)

Copy this formula down for as many rows as needed (through row 16 in your
original post).

2. In the cell where your IF formula was , enter:

=SUM(Miles!E:E)

If the BLANK sheet formulas are in a different column than E, change E:E to
that column.

Hope this helps,

Hutch

"bikky" wrote:


Thanks, i think it might have to be vb.

Its basically a milage form to submit for work, i have a sheet with a
named column for a validation drop down list for to and from, then also
on that sheet with the validation list is the miles accrued between
sites. eg.

sitea siteb 10
sitea sitec 12
sitea sited 27
siteb sitec 19
siteb sited 12
sitec sited 6

so
if cell 1 = miles!cell1 and cell2 = miles!cell2 then cell3,
else if cell 1=miles!cell4 and cell2 = miles!cell5 then cell6,
else......

Problem is with so many sites it would be a large if, and i didn't know
what the limit was. I need the local cell reference to be dynamic so it
can be copied from a blank template in the workbook to a new worksheet
for each month, however the lookup is to a fixed cell called miles.

Any help on this would be great as we're all sick of handwriting these
every month.

Thanks


--
bikky
------------------------------------------------------------------------
bikky's Profile: http://www.excelforum.com/member.php...o&userid=36149
View this thread: http://www.excelforum.com/showthread...hreadid=559359