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 I am understanding your question correctly you could use =IFERROR(VLOOKUP(@A:A,'RAW DATA'!C:F,4,FALSE),"0"). If you wanted to add another formula for #N/A you could use =IFERROR(VLOOKUP(@A:A,'RAW DATA'!C:F,4,FALSE),IF(.........). If you get an error the formula then moves to the second IF function
=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,""))- 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