• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Need help with a function formula...

K

Kristen_cancerkiller

Member
Can you tell me what is wrong with my function formula? If my 'if statement' is true, I get the appropriate response of 62494, however, if my 'if statement' is false, my vlookup is giving me an #ref! error?

=IF('Travel Expense Voucher'!$F$5=2,62494,VLOOKUP('Travel Expense Voucher'!M15,'Tcodes and Ecodes'!C11:D12,'Tcodes and Ecodes'!D11:D12,FALSE))
 
Hi ,

The syntax of the VLOOKUP function is like this :

=VLOOKUP(scalar lookup value , lookup range with one or more columns , column number , true/false)

So if your lookup range is 3 columns wide , if the column number is 2 , then the value from the second column corresponding to the matched row in the first column will be returned.

If your lookup range is 3 columns wide , if the column number is 3 , then the value from the third column corresponding to the matched row in the first column will be returned.

You need to change your formula to :

=IF('Travel Expense Voucher'!$F$5=2,62494,VLOOKUP('Travel Expense Voucher'!M15,'Tcodes and Ecodes'!C11:D12,2,FALSE))

The formula will return the value from 'Tcodes and Ecodes'!D11 if the value in 'Tcodes and Ecodes'!C11 matches the value in 'Travel Expense Voucher'!M15.

The formula will return the value from 'Tcodes and Ecodes'!D12 if the value in 'Tcodes and Ecodes'!C12 matches the value in 'Travel Expense Voucher'!M15.

Narayan
 
That was exactly what I needed. I can't believe I did that. So I basically had an additional argument that I didn't need, right? DUH... Thank you so much for your help!!
 
You have been so much help.... could I ask another question? I need to create a reference to look up a code based on if employee or non-employee are selected, then based on if the charge was for in-state or out-state, as well as the dollar amount listed. Is it possible to do all of these functions in one formula/function?
 
Yes, the file is attached. You will see on the first tab is the original that will be completed by staff which is the option of employee or non-employee. there is also an additional tab by that same title which is the table that I will use to create a pivot table to use for data entry. If you have any questions, please let me know. I am eager to see the results. :)
 

Attachments

  • TravelForm WorkingCopy3.xlsm
    96.3 KB · Views: 7
I should probably specify that if any of the charges in the lodging column are $12.00, they are considered 'non-receipt' lodging determined based on employee or non-employee
 
Hi ,

You have mentioned this :

I need to create a reference to look up a code based on if employee or non-employee are selected, then based on if the charge was for in-state or out-state, as well as the dollar amount listed.
Click to expand...

Which cell in which worksheet will have the reference ?

Narayan
 
Hi ,

So what will be the logic for deriving the values in U3 , U4, U5 and beyond ?

Can you specify the logic and also mention the resulting values for at least two or three cells ?

Narayan
 
Of course, I will use U3 to explain. If this is a non-employee ('Travel Expense Voucher'!$F$5=2) and T3 is not $12.00 - then per the Tcodes and Ecodes tab, the field should return 62497. If the value in T3 were $12.00, it should return 62498.
If in fact this was an employee ('Travel Expense Voucher'!$F$5=1) and T3 is not $12.00, and M3 shows In-State, then it should return (per the Tcodes and Ecodes tab) 62408, or if T3 is $12.00 then it should return 62406. Again if this is an employee and T3 is not $12.00 and M3 shows Out-State then 62418 should be returned. Or if it is $12.00, 62416 should be returned.
 
Hi ,

See if this IF statement fulfills the logic you have mentioned :

=IF('Travel Expense Voucher'!$F$5=2, IF(T3 <> 12,'Tcodes and Ecodes'!G6,'Tcodes and Ecodes'!G7),IF(T3 <> 12,IF(M3 = "In-State",'Tcodes and Ecodes'!G12,'Tcodes and Ecodes'!G13),IF(M3 = "In-State",'Tcodes and Ecodes'!G11,'Tcodes and Ecodes'!G14)))

If Non-Employee , if T3 = $12.00 then 62498.

If Non-Employee , if T3 is not equal to $12.00 then 62497.

If Employee , if T3 = $12.00 , if M3 shows "In-State" , then 62406.

If Employee , if T3 = $12.00 , if M3 shows "Out-State" , then 62416.

If Employee , if T3 is not equal to $12.00 , if M3 shows "In-State" , then 62408.

If Employee , if T3 is not equal to $12.00 , if M3 shows "Out-State" , then 62418.

Narayan
 
Hi ,

Sorry. I forgot the absolute addressing ; the correct IF formula will be :

=IF('Travel Expense Voucher'!$F$5=2, IF(T3 <> 12,'Tcodes and Ecodes'!$G$6,'Tcodes and Ecodes'!$G$7),IF(T3 <> 12,IF(M3 = "In-State",'Tcodes and Ecodes'!$G$12,'Tcodes and Ecodes'!$G$13),IF(M3 = "In-State",'Tcodes and Ecodes'!$G$11,'Tcodes and Ecodes'!$G$14)))

Narayan
 
OMG!! You are amazing!! This looks exactly like what I needed. :) Thank you so much!!! I have been battling in my head for a couple of weeks on how best to do this... you are truly an Excel Ninja! :)
 
One last one. I am getting an error on this formula, same workbook/spreadsheet. I assume I am missing something small but I'm not sure what. :)

Meals:

=IF('Travel Expense Voucher'!$F$5=2,'Tcodes and Ecodes'!$J$6),IF(G3=OVN),IF(M3 = "In-State",'Tcodes and Ecodes'!$J$12,'Tcodes and Ecodes'!$J$13),IF(G3=RTN),IF(M3 = "In-State",'Tcodes and Ecodes'!$J$11,'Tcodes and Ecodes'!$J$14)))

If Non-Employee, then 62495.

If Employee , if G3 shows OVN and M3 shows "In-State" , then 62410.

If Employee , if G3 shows OVN and if M3 shows "Out-State" , then 62430.

If Employee , if G3 shows RTN and if M3 shows "In-State" , then 62407.

If Employee , if G3 shows RTN and if M3 shows "Out-State" , then 62417
 
Try this:

=IF('Travel Expense Voucher'!$F$5=2, 62495, IF(AND(G3="OVN",M3="In-State"), 62410, IF(AND(G3="OVN",M3="Out-State"), 62430, IF(AND(G3="RTN",M3="In-State"), 62407, IF(AND(G3="RTN",M3="Out-State"), 62417, "")))))

You need to learn how to nest IF statements - get the nesting and parenthesis wrong and you'll be in a world of pain.
 
Hmmm... that isn't returning anything. :( Yes, I have issues with the nesting of IF statements.. I figured that was my issue but I couldn't figure out what I was missing.
 
Hi ,

Since you are trying to apply the initial IF statement to this new requirement in column W , let us see the correspondence between what was used in column U and what is required in column W.

1. The test T3 <> 12 checks for lodging ------ the corresponding check for column W would be G3 = "OVN"

2. The test for M3 would remain.

3. The result correspondences would be :

Instead of 62497 , you have 62495.

Instead of 62498 , you have nothing.

Instead of 62406 , you have 62407.

Instead of 62418 , you have 62430.

Instead of 62416 , you have 62417.

Instead of 62497 , you have 62495.

Replace these correspondences in the first IF statement , and you should have the correct version for column W.

=IF('Travel Expense Voucher'!$F$5=2, IF(G3 = "OVN",'Tcodes and Ecodes'!$J$6,'Tcodes and Ecodes'!$J$7),IF(G3 = "OVN",IF(M3 = "In-State",'Tcodes and Ecodes'!$J$12,'Tcodes and Ecodes'!$J$13),IF(M3 = "In-State",'Tcodes and Ecodes'!$J$11,'Tcodes and Ecodes'!$J$14)))

See if this is correct.

Narayan
 
Just remember to count the parenthesis on the way in and make sure you mirror it on the way out.

So, if you have 5 x IF( in the formula, you need 5 x ) at the end. That's what correct nesting looks like.
 
Thank you. I will be taking an advanced Excel class in the near future and that will help me I'm sure. :) Hopefully, someday, I will be helping others with questions on this forum. :)
 
You will. I started looking for help on my first Excel forum (not this one) in 2013 and now I'm not only a regular helper, but also a moderator. I have learned SO much from participating on forums - far more than I'd ever have learnt from any course, to be honest. Never had an IT lesson in my life. :)

I suspect in your case learning how to use lookups will help you - there's probably a much simpler way of doing what you want that does not involve nested formulae at all.
 
That would be nice. LOL I'll figure it out. I suspect with getting this form figured out, with all of the people's help on this forum, I might have even more that my boss will ask me to do. LOL What did I get myself into?

I do have one more question, I have the following formula in my document, which works like a dream, however, when I have no values in the initial field, I would like to populate a '-' or have it blank. Any help on that?

=IF(VLOOKUP($L15,Table1,2,FALSE)>2,"-",O15*P15)
 
You must log in or register to reply here.
Back
Top