I’m making a real pigs ear of this… maybe I’m trying to do too much in one jump.

I’m not great with excel, don’t work in IT, but am trying to set up a spreadsheet along these lines:

There are 10 shops
Each shop stocks 10 categories of product
For each category every shop is graded from A to E (variable and specified on a table)

I have a line of information on one sheet which specifies the category and the qty required for each grade of shop.

I want to link that to an order specific for each shop.

So at the moment I have…

IF(category,IF(grade=A,(qty),IF(grade=B,(qty),IF(grade=C,(qty),IF(grade=D,(qty),IF(grade=E,(qty),”0″))))))

Which works for one category, but I don’t know how to develop this for variable categories..

(Hope the formula makes sense, the italics are obviously links in real life)

Any help / suggestions are gratefully received… including the suggestion to scrap it all and do it on paper instead 😳

Stick a sample spreadsheet on dropbox/google/somewhere for folks to lok at. There are various options such as VLOOKUP, Pivottables etc. but it isn’t completely clear what you are trying to do – sorry

Range & vlookup?

Leffeboy… I will email a test sheet to you. Feel free to share it if you don’t mind.

Think VLOOKUP will just provide a more elegant version of what OP already has, but can’t lookup when there are two input variables (category and grade)?

Edit:Actually, VLOOKUP is half the answer.
OP, think this link offers some solutions to your problem:

VLOOKUP on Two or More Criteria Columns

IF(category,IF(grade=A,(qty),IF(grade=B,(qty),IF(grade=C,(qty),IF(grade=D,(qty),IF(grade=E,(qty),”0″))))))

you could do it with another level of nesting IFs, if you wanted to

I would create an array with 2 axis category and grade. Then complete the table

Then use the following formula to find the value at the right intersection of grade and category

=INDEX(data,MATCH(val,rows,1),MATCH(val,columns,1))

example

You can use the ‘ifs’ function to consider multiple conditions together. But vlookup or index&match will probably be a cleaner solution, depending on what you actually need to do.

Leffeboy… I will email a test sheet to you. Feel free to share it if you don’t mind.

I didn’t get anything :(. Did you send it to the email in my profile?

@chrismac’s idea sounds about right though but fire a sheet through and I’ll put it online for you

https://docs.google.com/spreadsheets/d/1qYO59GAp_Q7xyKVCrenxEMPh6FvFI8hqF6ResxXfqMs/edit?usp=sharing

I think that this should share a test doc.

It is all part of a bigger spreadsheet, but this should show where i’m trtying to get…

Product Info – this is a huge table of data, have just created the relevant columns
Order – This is the sheet I am trying to populate
Grade tables – these are also variable.

Hope this makes sense…

Jo

@leffeboy – yes, I used the one in the profile, have tried again, as the sheet I emailed has my existing formula in… Struggling to copy that onto the google sheets.

You could concatenate the store and category to create a unique ID for each combination.
So, if your stores are in column A and the categories are in column B you could have another column with the formula:

=A2&”¦”&B2

which would give the combination of store and category A a unique ID, e.g.:

Store1¦A
Store1¦B
Store1¦C
Store2¦A
Store2¦B
etc

Then you can use the ID field to do a V or HLOOKUP to your list of store grades.

Alternatively, you can do a SUMIFS to get the sum from the “qty required” sheet on a combination of product categrory and grade.
.
.
.
.
As I’m typing I think I’m getting a clearer idea of what you want and what you’ve got.

So, one worksheet is “Quantity required”. You’ve got three columns A:”Product Category”, B:”Store Grade”, c:”Qty Required”.

A second worksheet is “Stores”. This has three columns again: A:”StoreID”, b:”Product Category”, c:”Grade”.

You want to add a fourth column to the Stores sheet showing what quantity of items should be ordered for each combination of store and category? Is that about right?

So, you could definitely use a sumifs:

=SUMIFS(‘Quantity Required’!C:C,’Quantity Required’!A:A,Stores!B2,’Quantity Required’!B:B,Stores!C2)

edit: You posted the doc while I was typing!

@bails – there could be a huge number of product lines – upto 10,000.

The product lines are all on one sheet

Each line will have a category (lights, trees, general, cards) and the qty for each of a A,B,C,D,E grade store.

The store grade are on a different tab, and will vary.

The third tab is the order. I would like to populate this for each product line, for each shop. But in a way that if the grade are altered the order alters accordingly…

@ charliemungus & twisty – I can try another level of ‘if’, but it is just getting so ‘clunky’ I was wondering if there was a better solution.

Thanks for the input so far 🙂

@bails has done it the same way as the link I provided.

Looking at the sheet you posted, it seems a perfect candidate for this:

chrismac – Member
I would create an array with 2 axis category and grade. Then complete the table

Then use the following formula to find the value at the right intersection of grade and category

=INDEX(data,MATCH(val,rows,1),MATCH(val,columns,1))

Thank you 🙂

I really appreciate the help. Sorry Fifeandy I missed your edit with the link first time around.

Oops sorry, was out for lunch. Sounds like you have it now

Please tell me you’re not manually calculating the summary table in column W of the store grade sheet?! 🙂

^ using COUNTIF to sum, but just set that up as a test for the other problem 🙂