IF or VLOOKUP

[HansJansen]

Hello everybody!

I am new here, I came across this forum through Google.

For my studies I am making a price list on Excel 2003 for various destinations. Basically I have a list of 16 'irregular' destinations where extra costs apply.

For example like this:

Total cost:

- Cost A +
- Cost B +
- Cost C +
- Cost D (Each irregular product has its own fixed cost, so 16 different costs)

At first, I made a list of the 16 irregular products, and next to each I added the cost. And then I tried doing =IF(D30="Product 1",'sheetname'!U9,IF(D30="Product 2",'sheetname'!U10 .... etc etc ))

However, I can only add 6 ''IFs'' in Excel 2003 (more in 2010, but I need it in 2003).

So what are my options, I read something about Vlookup, but Im not sure how that would work, how should my list look like etc? Any ideas are highly appreciated!

Thanks very much.

Hans

 

[JackDanIce]

Welcome to the board.

You're right, Excel 2003 is limited to 7 nested IFs. VLOOKUP would be a better approach. VLOOKUP works as:

=VLOOKUP(your search item, range/area to search in, column to return, type of match)

so a simple function would be:

=VLOOKUP(A1,$B$1:$D$5,3,0)

Here, your search item is whatever is in cell A1, your search area is B1:D5; VLOOKUP always tries to find the match in the first column, e.g. B1:B5. If a match is found, 3 tells the function to return the value from the 3rd column of that range from the same row the match is found in. 0 is telling VLOOKUP to search for an exact match only

In your case I think your formula would be:

=VLOOKUP(D30,'sheet name'!$A1:$B$10,2,0)

As example, I'm making an arbitary guessy your search range is in a sheet called "Sheet name" and in the range A1:B10 and you want to return whatever value matches from the 2nd column

 

[HansJansen]

Dear JackDanIce,

Your formula worked PERFECTLY! Thank you so much for taking the time to explain this

Wish you a great day! Thanks again,

Hans

 

[JackDanIce]

You're welcome. If you've not used VLOOKUP before, I highly recommend learning more about it - it's a good way to learn more about Excel and what else it can do

 

[HansJansen]

Yeah, this worked much better than doing using the IF function, I will definately read more about it, cheers!