Shorten a Formula

[Kevin]

I have a formula that is pretty long. It works, but I have to propagate it
into 500+ other cells and the editing will take a long time.

Anyway, please take a look and see if there is any way I can shorten this
up. I'm sure there is, but I'm at my limits.

=IF(ISNA(IF(ISNA(VLOOKUP(A6,A99:A168,1,FALSE)=A6),(0+VLOOKUP(A6,G99:H168,2,FALSE)),IF(ISNA(VLOOKUP(A6,G99:G168,1,FALSE)=A6),(0+VLOOKUP(A6,A99:B168,2,FALSE)),VLOOKUP(A6,A99:B168,2,FALSE)+VLOOKUP(A6,G99:H168,2,FALSE)))),0,IF(ISNA(VLOOKUP(A6,A99:A168,1,FALSE)=A6),(0+VLOOKUP(A6,G99:H168,2,FALSE)),IF(ISNA(VLOOKUP(A6,G99:G168,1,FALSE)=A6),(0+VLOOKUP(A6,A99:B168,2,FALSE)),VLOOKUP(A6,A99:B168,2,FALSE)+VLOOKUP(A6,G99:H168,2,FALSE))))

Thanks,

Kevin

 

[bpeltzer]

Could you tell us what this is supposed to do? It's riddled with
redundancy!! As for the difficulty of copying it, I wonder if the challenge
is just because the tables used in the vlookup use relative references. That
is, if you make the vlookup something like vlookup(a6,$a$99:$a$168,1,false)
instead of vlookup(a6,a99,b168,1,false), then when you copy the formula, the
table reference will remain constant ('absolute').
--Bruce

 

[Kevin]

I know it's a horrible thing to look at, but I don't have much (if any)
experience with formulas, so it's what I got to work.

It's supposed to take an exact match from A6, compare it to the Vlookup. If
it's there, look in the other Vlookup for A6. If A6 is in the second Vlookup,
add the two values in the 2nd column of the Vlookup's together and drop in
the cell. If A6 results in N/A, it should assign a zero for the N/A in
either lookup when adding them together.

Sometimes A6 will exist in both lookups, sometimes only in one, and
sometimes never. I have to at least have a result of 0 in the final result,
and never an N/A, for my import into another database from the spreadsheet to
work.

Thank you for the "absolute" information. That saved me a ton of time
copying the formula. I would like to make it a bit more logical, so if you
have any advice it would be appreciated.

Again, thanks for the help.

Kevin

 

[daddylonglegs]

You should only need this

=IF(ISNA(MATCH(A6,A99:A168,0)),0,VLOOKUP(A6,A99:B168,2,0))+IF(ISNA(MATCH(A6,G99:G168,0)),0,VLOOKUP(A6,G99:H168,2,0)

 

[Kevin]

Thanks, it works great.

Kevin