Excel 2007 : IF, AND or VLOOKUP formatting or a mixture of both....

**Garethrowe** · 02-17-2011, 06:05 AM

I am trying to improve efficiency by utilizing Excel functions.

We stock take a large number of products on a monthly basis and their prices are fluctuating all the time. The items we stock take are denoted by specification and size. There are multiples sizes associated to a single specification.

I want to be able to input a input a specification and size of a product in 1 sheet and then allow the spreadsheet to lookup a cost that is part of a 'Master cost' list in another sheet.

I’m unsure if I need to use IF, AND or VLOOKUP formatting or a mixture of both....

Any help would be appreciated.

**DonkeyOte** · 02-17-2011, 07:08 AM

If the 'Master Cost' list sorted in any way ?

Where is the information located ?

Is the "specification" repeated for each "size" entry ?

It would be a good idea to post a small sample file that reflects what you're working with - obviously dummy anything that would be confidential.

**Garethrowe** · 02-17-2011, 07:36 AM

Right the Master Cost list is sorted as jpg attached named master cost

In sheet 1 of a wooksheet there is the inputted information and sheet 2 there is the master cost data. Sheet 1 is layed out as per attached 'information.jpg

Obviously the cost column is were i want the cost to be automatically inputted from the master cost sheet.

I hope the above makes sense.

Gareth

**DonkeyOte** · 02-17-2011, 07:55 AM

Images are of limited use I'm afraid (unless you want help with an image) - people generally don't have the time to recreate your data.

Key question: Regards the 'Master Cost' sheet are the sizes listed identical for each Spec. ?

**Garethrowe** · 02-17-2011, 08:11 AM

Key question: Regards the 'Master Cost' sheet are the sizes listed identical for each Spec. ? Yes sizes are identical but costs differ!

See attached worksheet

**DonkeyOte** · 02-17-2011, 08:24 AM

Given sample and the above:

Please Login or Register  to view this content.

**Garethrowe** · 02-18-2011, 06:17 AM

Right I did have the code working until I entered my correct Specification, sizes and costs in sheet2 - now all i get is a return figure of 0?

Any suggestions

I have attached my worksheet....

**DonkeyOte** · 02-18-2011, 06:21 AM

You've changed the order of your Sizes so you need to adjust the 2nd MATCH match_type from default of 1 (omitted previously) to -1

Please Login or Register  to view this content.

Given you're now repeating the Specification for each Size you might also consider a SUMIFS

Please Login or Register  to view this content.

the latter will return 0 where the Spec & Size combination does not exist whereas the former will return an error (NA) - you can handle the error with an IFERROR given use of XL2007+

**Garethrowe** · 02-18-2011, 10:13 AM

Thanks for the help - all is working fine!

Excel 2007 : IF, AND or VLOOKUP formatting or a mixture of both....

LinkBack

Thread Tools

Rate This Thread

Display

IF, AND or VLOOKUP formatting or a mixture of both....

Re: IF, AND or VLOOKUP formatting or a mixture of both....

Re: IF, AND or VLOOKUP formatting or a mixture of both....

Re: IF, AND or VLOOKUP formatting or a mixture of both....

Re: IF, AND or VLOOKUP formatting or a mixture of both....

Re: IF, AND or VLOOKUP formatting or a mixture of both....

Re: IF, AND or VLOOKUP formatting or a mixture of both....

Re: IF, AND or VLOOKUP formatting or a mixture of both....

Re: IF, AND or VLOOKUP formatting or a mixture of both....

Thread Information

Users Browsing this Thread

Bookmarks

Bookmarks

Posting Permissions