# Thread: Nested IF (Excel 2003)

1. ## 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

2. ## 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. ## 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. ## 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. ## 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. ## 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. ## 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. ## Re: Nested IF (Excel 2003)

ahhhhhhhhhhh thank you

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

9. ## 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. ## 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,

11. ## 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. ## 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.

#### Posting Permissions

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