Results 1 to 12 of 12
  1. #1
    Star Lounger
    Join Date
    Nov 2007
    Posts
    65
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Nested IF (Excel 2003)

    hi there
    i need a nested IF so in (Reports) B4 cell i need to calculate that if A4 in (Product Code) is the same as A4:A20 in (Price List) then the answer is B4 in (Price List). (i need the codes in range A4 to A20 to match the price in the next cell)
    then i will just copy the formula down.

    do you know of any good books on Nested If and Function i have quite a few and can never find what im looking for or it never works right.
    thanks i do appreciate the help, kitty
    Attached Files Attached Files

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Nested IF (Excel 2003)

    I don't think there are books that focus on nested IF formulas, but John Walkenbach's Excel 2003 Formulas is a good book on working with formulas and functions.

  3. #3
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Nested IF (Excel 2003)

    Why do you need a nested IF? If I understand your question correctly, you just need a VLOOKUP formula, similar to the one you have in the Product Sales sheet (but without the multiplication, since you want the item price here).

  4. #4
    Star Lounger
    Join Date
    Nov 2007
    Posts
    65
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Nested IF (Excel 2003)

    hi hans
    ive tried the vlookup but i keep getting it wrong, i think my understanding of them needs upgrading thats why i come on to your site to practice before i start my computer classes next year. can you help me with another vlookup please.

    kitty

  5. #5
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Nested IF (Excel 2003)

    It is *exactly* the same formula as on the Product_Sales sheet without the multiplication:

    =VLOOKUP(A4,Price_List!price_list,2,FALSE)

    Can you explain what went wrong when you tried to apply the same formula?

  6. #6
    Star Lounger
    Join Date
    Nov 2007
    Posts
    65
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Nested IF (Excel 2003)

    hi hans
    i keep missing simple things like commas and brackets, im sorry to be thick, but i just don't no where they go or understand why they are in the places they are, ive had no training just reading books that make little sense so when i found your site i was delighted and i usually ask why the formula is arranged the way it is so i have a better understanding, i can do the basics and im really good at word now.
    thanks again x

  7. #7
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Nested IF (Excel 2003)

    You can look up the VLOOKUP function in the Excel help, or here: VLOOKUP - Excel - Microsoft Office Online. It has four arguments:

    VLOOKUP(lookup_value,table_array,col_index_num,ran ge_lookup)

    of which the first three are required, and the last one is optional. The arguments are separated by commas (or more accurately, by the "list separator" character on your PC).

    lookup_value is the value you want to look up - in your example the product code.

    table_array is the multi-column list in which you want to search. VLOOKUP will go down the first column to look for a match of the lookup_value.
    In your example, this is the price_list range on the Price_List sheet.

    index_num tells Excel from which column in table_array the return value should come - in your example it is 2 since you want to return the price from the 2nd column of price_list.

    range_lookup can be TRUE or FALSE; it specifies whether you want an approximate match or not.
    If you specify TRUE or omit range_lookup, Excel looks for the largest value in the first column that is less than or equal to the lookup_value. This only works correctly if the first column is sorted in ascending order.
    If you specify FALSE (as in your example), Excel looks for an exact match. If it can't be found, the function returns #N/A (not available).

    So in the formula =VLOOKUP(A4,Price_List!price_list,2,FALSE)

    lookup_value = A4
    table_array = Price_List!price_list
    index_num = 2
    range_lookup = FALSE

  8. #8
    Star Lounger
    Join Date
    Nov 2007
    Posts
    65
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Nested IF (Excel 2003)

    ahhhhhhhhhhh thank you

    will read it tomorrow, just going to bed, nite
    kitty

  9. #9
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Nested IF (Excel 2003)

    In addition to Hans' explanation and the help, if you press the equal sign (=) in teh formula bar after entering in the function, a dialog box will popup that allows you to fill in the various sections and gives help on each of them.

    Steve

  10. #10
    3 Star Lounger
    Join Date
    Mar 2003
    Location
    Beddau, Mid Glamorgan, Wales
    Posts
    289
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Nested IF (Excel 2003)

    Hi Kitty:

    Just in case of confusion, in Excel 97 the button is an equal sign (=); in Excel 2003 it is 'fx'.

    The tooltip says 'Insert Fuction' when you hover the mouse over it.

    Regards,
    Regards,

    Tony
    [s] [/s]
    www.SylviArtist.com

  11. #11
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Nested IF (Excel 2003)

    Thanks for the clarification. XL2002 also has the function of X "fx" symbol as the "insert function". I don't know about XL2000 or XL2007...

    Steve

  12. #12
    3 Star Lounger
    Join Date
    Mar 2003
    Location
    Beddau, Mid Glamorgan, Wales
    Posts
    289
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Nested IF (Excel 2003)

    I mentioned 2003 as that was the version the OP indicated. 2000 uses the '=' sign - I don't know about 2007, as I haven't installed it yet.
    Regards,

    Tony
    [s] [/s]
    www.SylviArtist.com

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •