formula help

[Southpaw19]

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 to change it to zero.

=VLOOKUP(@A:A,'RAW DATA'!C:F,4,FALSE) this is the

Plus another formula 

=IF(B8<0,"CLAWBACK","FILL IN LATER") this is the beginning of it and want to added Vlookup after it

[#2]

@Southpaw19 

Hello! You've posted your question in the Tech Community Discussion space, which is intended for discussion around the Tech Community website itself, not product questions. I'm moving your question to the Excel space - please post Excel questions here in the future. 

[#3]

I guess below is what you want.
=IFNA(VLOOKUP(@A:A,'RAW DATA'!C:F,4,0),0)
or
=IFNA(VLOOKUP(@A:A,'RAW DATA'!C:F,4,0),IF(B8<0,"CLAWBACK","FILL IN LATER") &" 0")

[#4]

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

[#5]

=IFERROR(VLOOKUP(A:A,'RAW DATA'!C:H,6,FALSE),0) I used this formula and i am getting #splii! now
I used this same formula on different column and it work

[#6]

=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

[#7]

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,""))

[#8]

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

[#9]

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 :)

[#10 · Accepted solution]

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

[#11]

on this formula, i need to added if finds #N/A changed it to Previous Balance Forward

[#12]

Thank you very much that work perfect

[#13]

Currently this formula will change #N/A to 0
=IFERROR(VLOOKUP(A16,'RAW DATA'!C:H,6,FALSE), 0)
If you want to change it to something else you could do the following
=IFERROR(VLOOKUP(A16,'RAW DATA'!C:H,6,FALSE), "Previous Balance Forward") now it will change it the text "Previous Balance Forward", you can also have it reference a cell

[#14]

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)))))

[#15]

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")

[#16]

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

[#17]

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!