Scheuerman1987, this sounds more like a help-me-understand question than a please-solve-my-problem question, so I'm going to ignore your workbook and switch to Socratic questions:



1) Do you know how the IF function works by itself? If you do, give an example of an IF call; if you don't, say so and I'll explain.

2) Do you know how to use the VLOOKUP function by itself? If you do, give an example or explain it in your own words; if not, tell me as much as you DO understand about it and I'll fill in the gaps.

Once we've gone over the two pieces, it should be easy enough to explain how to use them together—if by then you still need us to.

Well I know how to use the functions separately. It's nesting them that I am trying to figure out so that the formula works.

The IF Function: IF(logical_test, [value_if_true,] [value_if_false])

Ex: IF(C33>=$L$11, $L$10, 0)

So that's saying if L11 = 1000
L10 = 800

So if cell C33 is greater than or equal to the value in cell L11, the formula returns the value in cell L10.

If the value in cell C33 is less than the value in cell L11 the formula will retunr a 0.

VLOOKUP Function: VLOOKUP(Lookup_value, Table_array, Col_Index_num, Range_lookup)

EX: VLOOKUP(Years Service, G4:H8, 2)

So this would look at the employee's years of service. It would look at G4:H8 because that is where you told Excel to look and that the information or award you want returned is stored in column 2. You don't have to use the range_lookup if you omit that then it makes VLOOKUP an approximate match lookup.

With all that said I just need to know how I'd go about nesting the 2 functions together.

Ok, just checking. So the first IF function is supposed to check $B$17; if it's blank, then the IF statement should return a blank, otherwise it should do a VLOOKUP in some table for the value in $B$17. The IF, then, says

=IF($B$17="","",lookup)

I don't know what the exact VLOOKUP is, but let's pretend it looks like this:

=VLOOKUP($B$17,OtherWorksheet!$A:$C,3,0)

(That searches in column A of a worksheet named OtherWorksheet, looking for a value that matches $B$17 in this worksheet; if it finds a match in A27 (say), then it returns the value in C27.)

To combine them is simple; you just put the VLOOKUP call inside the IF call, where I wrote "lookup" before, like this:

=IF($B$17="","",VLOOKUP($B$17,OtherWorksheet!$A:$C ,3,0))

Note that you don't duplicate the '='; that's not actually part of the function call, it only introduces to Excel that you're about to do a calculation of some kind. But everything else is copied exactly, including all the parentheses.

Make sense?

Thank you so much for explaining how to nest these two functions together. It made it a lot easier for me to figure out this assignment and complete it!! I always get confused when it comes to nesting. I had never seen these two functions used together. I don't think my text book explained nesting very well either.