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 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
- 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
16 Replies
- 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- 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,""))
- =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
- 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") 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.
