Vlookup, sumproduct, if, sumif... erggg!
 

Sigh.... Can only get it to work halfway. I'm thinking I've had to much
caffeine and overlooking something. Or my very basic skills just are not up
to the task. LOL Sometimes a little knowlege sometimes gets dangerous!

What I have and need:
*2 worksheets:BILLS(think checkbook register), RAP (think bank statement)
*if column L on RAP matches column W on BILLS return value in column P on
BILLS even if it does not match. Sometimes things are slightly off due to
taxes.
*I then use this number to subtract from column O on RAP to show differences
froms the logged bills vs the RAP so I can filter down to the ones that don't
balance and research why. I'm balancing a high volume of invoices and looking
for a solution without having to go invoice by invoice for 3000+ invoices
every month.

What I tried that somewhat works:
=IF(ISERROR(VLOOKUP($L2,Bills!P$2:$W450,1,FALSE)), 0,VLOOKUP($L2,Bills!P$2:$W450,1,FALSE))

=VLOOKUP($O4,Bills!P$2:$W452,1,FALSE)

my sumif and sumproduct just went to #value.

Thanks,
Kenirae

Problem:
=IF only returns the value in column P of BILLS if column O on RAP matches
exactly. I want it to post the amount if the L & W matches if if there is no
$$ logged in P.

=Vlookup only returns the value in column P of BILLS if column O on RAP
matches exactly. I want it to post the amount if the L & W matches if if
there is no $$ logged in P. Right now it returns #n/a if no exact match.

Vlookup, sumproduct, if, sumif... erggg!
 

If desired, send your file to my address below. I will only look if:
1. You send a copy of this message on an inserted sheet
2. You give me the newsgroup and the subject line
3. You send a clear explanation of what you want
4. You send before/after examples and expected results.


--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"Serendipity" wrote in message
...
Sigh.... Can only get it to work halfway. I'm thinking I've had to much
caffeine and overlooking something. Or my very basic skills just are not
up
to the task. LOL Sometimes a little knowlege sometimes gets dangerous!

What I have and need:
*2 worksheets:BILLS(think checkbook register), RAP (think bank statement)
*if column L on RAP matches column W on BILLS return value in column P on
BILLS even if it does not match. Sometimes things are slightly off due to
taxes.
*I then use this number to subtract from column O on RAP to show
differences
froms the logged bills vs the RAP so I can filter down to the ones that
don't
balance and research why. I'm balancing a high volume of invoices and
looking
for a solution without having to go invoice by invoice for 3000+ invoices
every month.

What I tried that somewhat works:
=IF(ISERROR(VLOOKUP($L2,Bills!P$2:$W450,1,FALSE)), 0,VLOOKUP($L2,Bills!P$2:$W450,1,FALSE))

=VLOOKUP($O4,Bills!P$2:$W452,1,FALSE)

my sumif and sumproduct just went to #value.

Thanks,
Kenirae

Problem:
=IF only returns the value in column P of BILLS if column O on RAP matches
exactly. I want it to post the amount if the L & W matches if if there is
no
$$ logged in P.

=Vlookup only returns the value in column P of BILLS if column O on RAP
matches exactly. I want it to post the amount if the L & W matches if if
there is no $$ logged in P. Right now it returns #n/a if no exact match.