Results 1 to 12 of 12
Thread: Nested IF (Excel 2003)

20071210, 22:11 #1
 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

20071210, 22:18 #2
 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.

20071210, 22:29 #3
 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).

20071210, 22:45 #4
 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

20071210, 22:53 #5
 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?

20071210, 23:11 #6
 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

20071210, 23:37 #7
 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 multicolumn 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

20071211, 00:54 #8
 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

20071211, 01:14 #9
 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

20071212, 15:27 #10
 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

20071212, 15:51 #11
 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

20071213, 12:09 #12
 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