Forum Discussion
Nested IF function or???
Here's a very simple example of how VLOOKUP can be used to return a value, based on a selection from a drop down cell. Select the city name, and the value will appear in the yellow background cell, which is where the formula is. This is a very simple example. VLOOKUP can be used with a much more complex table, with more columns. See that reference in my last message for more on how to use it.
Thank you so much for these references. I think the VLOOKUP may work, BUT what I need it to do after that is take that returned value and multiply the % with the value of another cell to return the monetary value I am looking for in the cell with the formula. Let me start from the beginning. Below is a timesheet. Ideally, a pay rate would be entered into the N column. Based on the chosen value in B-11 where the site dropdown list is the O column would then take the % that VLOOKUP found which matches that site and add the N column value to the N column value * the returned percentage to populate the correct billing rate for that pay rate or =(N18)+(N18*45%).
The tables where the percentages and sites are located is on the next tab.
I created a second table for the sites, but I am not sure I needed to and that could be complicating things a bit.
So, if a payrate is plugged into the N column the worksheet should do the math for all the rates based on the returned percentage for a given site and spit out a total invoice. I hope you have better luck than I have. I cannot seem to get around how to tie all the info together and make it do what I need it to. Maybe there is too much info needing to be calculated??
Thanks so much again for your help on this. I am so stumped, but I know there has to be a way. I love Excel for these kinds of projects.
Unfortunately the images are too hard to see......
But let me assure you that VLOOKUP can be embedded in another formula, to multiple a given value by a percentage, or increase it by a percentage that is itself looked up in a table.
(I had a career in Human Resources, worked with payroll data all the time; Excel is fully capable of handling your situation. We just need to get a working example that works for you.)
So I've added a level of complexity here to the sample file. Again just an illustration of what's possible.
This doesn't by chance help, does it?
https://docs.google.com/spreadsheets/d/1SaRJk046WFj1EyHN-29lmvS77cBwJOne/edit?usp=sharing&ouid=114382848188657363105&rtpof=true&sd=true
- mathetes
Ok, I figured out the VLOOKUP part with this =VLOOKUP(B11,'Dropdown Lists'!A3:D91,2,FALSE). Itt brings back 0.45, which is correct. Now I need it to take that 0.45 (as a percent) and multiply it by the value in another cell. How do I embed a formula into the one I have already in order for the worksheet to do the math? The above link will take you to the worksheet.
Thanks a million!!