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
=IF(D16=2.5,"PROCESSING FEES",IF(D16=1.5,"PROCESSING FEES",IF(D16=1,"PROCESSING FEES",IF(B16<0,"CLAWBACK",VLOOKUP(A16,'BALANCE FORWARD AFTER'!$A:$F,6,FALSE)))))
I am using this formula and want to add another vlookup from another tab
I am using this formula and want to add another vlookup from another tab
You can simplify that first part using OR. So =IF(D16=2.5,"PROCESSING FEES",IF(D16=1.5,"PROCESSING FEES",IF(D16=1,"PROCESSING FEES",IF(B16<0,"CLAWBACK",... becomes =IF(OR(D16=1,D16=1.5,D16=2.5),"Processing Fees",IF(B16<0,"Clawback",...
It will function the same but you can reduce your formula, then if you want to add more numbers you can just add it to the OR.
As for the vlookup, it's hard for me to say without seeing the data. But assuming your data is on sheet "Balance Forward After" in column A and you want to retrieve the data in Column F I would change VLOOKUP(A16,'BALANCE FORWARD AFTER'!$A:$F,6,FALSE) to XLOOKUP(A16,'BALANCE FORWARD AFTER'!$A:$A,'BALANCE FORWARD AFTER'!$F:$F,""). Both Vlookup and Xlookup will work, Xlookup is just a little more powerful than Vlookup.
Then if we want to add another xlookup (or vlookup) you replace the "" at the end with that formula. So it will become XLOOKUP(A16,'BALANCE FORWARD AFTER'!$A:$A,'BALANCE FORWARD AFTER'!$F:$F,XLOOKUP(A16,'The Other Tab'!$A:$A,'The Other Tab'!$F:$F,""))
It will function the same but you can reduce your formula, then if you want to add more numbers you can just add it to the OR.
As for the vlookup, it's hard for me to say without seeing the data. But assuming your data is on sheet "Balance Forward After" in column A and you want to retrieve the data in Column F I would change VLOOKUP(A16,'BALANCE FORWARD AFTER'!$A:$F,6,FALSE) to XLOOKUP(A16,'BALANCE FORWARD AFTER'!$A:$A,'BALANCE FORWARD AFTER'!$F:$F,""). Both Vlookup and Xlookup will work, Xlookup is just a little more powerful than Vlookup.
Then if we want to add another xlookup (or vlookup) you replace the "" at the end with that formula. So it will become XLOOKUP(A16,'BALANCE FORWARD AFTER'!$A:$A,'BALANCE FORWARD AFTER'!$F:$F,XLOOKUP(A16,'The Other Tab'!$A:$A,'The Other Tab'!$F:$F,""))
- So the result is this:
=IF(OR(D16=1,D16=1.5,D16=2.5),"Processing Fees",IF(B16<0,"Clawback",XLOOKUP(A16,'BALANCE FORWARD AFTER'!$A:$A,'BALANCE FORWARD AFTER'!$F:$F,XLOOKUP(A16,'The Other Tab'!$A:$A,'The Other Tab'!$F:$F,""))))
Just adjust the formula for your sheet name and the correct columns on "The Other Tab" and you should be good to go- 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)))))
- Thank you very much
=IFERROR(VLOOKUP(A:A,'RAW DATA'!C:H,6,FALSE), 0) using this formula on another column but i am getting #spill! error not sure why thou. I am learning the formulas 🙂- 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