Forum Discussion
Solved
formula help
HI there Need help adding more information to the formula I am using vlookup but i want to added another formula to it. I have #N/A from the vlookup and when there is #N/A in it, i want to be able ...
- Add an "@" before your lookup value =IFERROR(VLOOKUP(@A:A,'RAW DATA'!C:H,6,FALSE), 0). This will only work if the cell you are looking up is in the same row as this formula. Either that or reference a specific cell =IFERROR(VLOOKUP(A16,'RAW DATA'!C:H,6,FALSE), 0). YOu are getting the Spill error because it's trying to look up the whole column
on this formula, i need to added if finds #N/A changed it to Previous Balance Forward
Sorry for the questions, if finds #N/A, need to changed it to Previous Balance Forward in this formula
=IF(D5=2.5,"PROCESSING FEES",IF(D5=1.5,"PROCESSING FEES",IF(D5=1,"PROCESSING FEES",IF(B5<0,"CLAWBACKS",VLOOKUP(A5,'BALANCE FORWARD AFTER'!$A:$F,6,FALSE)))))
=IF(D5=2.5,"PROCESSING FEES",IF(D5=1.5,"PROCESSING FEES",IF(D5=1,"PROCESSING FEES",IF(B5<0,"CLAWBACKS",VLOOKUP(A5,'BALANCE FORWARD AFTER'!$A:$F,6,FALSE)))))
- No worries! Just add an IFERROR at the very beginning: =IFERROR(IF(D5=2.5,"PROCESSING FEES",IF(D5=1.5,"PROCESSING FEES",IF(D5=1,"PROCESSING FEES",IF(B5<0,"CLAWBACKS",VLOOKUP(A5,'BALANCE FORWARD AFTER'!$A:$F,6,FALSE))))),"Previous Balance Forward")
- Thanks one more thing
That work but I just realized i need 2 pieces when it finds N/A
If N/A needs to check another column which is column H to see if the Processing Fee is $0.00 if so then that N/A becomes NO PROCESSING FEES and all the other N/A would become Previous Balance Forward. I know this long formula now. First time at my work that i have to learn on these formulas and need to take Excel class now 🙂 This formula should do it now and I will be good to go- No worries. Try this and let me know if there's any problems with it.
=IFERROR(IF(D5=2.5,"PROCESSING FEES",IF(D5=1.5,"PROCESSING FEES",IF(D5=1,"PROCESSING FEES",IF(B5<0,"CLAWBACKS",VLOOKUP(A5,'BALANCE FORWARD AFTER'!$A:$F,6,FALSE))))),IF(H5=0,"No Processing Fee","Previous Balance Forward"))
This stuff takes time to learn so just be patient with it! Over time they become easy as you work with it more. I would definitely google Xlookup. It has made much life much easier compared to vlookup but both work!