Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Complex SUMPRODUCT/VLOOKUP/SUMIF | Excel Worksheet Functions | |||
VLOOKUP, SUMPRODUCT, or SUMIF? | Excel Worksheet Functions | |||
HELP Sumif or Sumproduct | Excel Discussion (Misc queries) | |||
Which SumProduct Sumif or VLookup? | Excel Discussion (Misc queries) | |||
I've tried Sumproduct, SumIf, Vlookup and Hlookup. | Excel Worksheet Functions |