Excel Help/ Question

[nikaiyo2]

I dont think what I want to do is possible, but maybe someone who knows how to use excel properly might have a pointer




This is the very basic test sheet, can I write a formula to return the "rate" for Red Hats India? In response to the entry in J.

So if the text is entered in J is Red Hats India it returns 4, Blue Hats Vietnam would return 6 etc.

[paulrockliffe]

nikaiyo2 said:

I dont think what I want to do is possible, but maybe someone who knows how to use excel properly might have a pointer




This is the very basic test sheet, can I write a formula to return the "rate" for Red Hats India? In response to the entry in J.

So if the text is entered in J is Red Hats India it returns 4, Blue Hats Vietnam would return 6 etc.

Everything is possible in Excel!

What you're describing is a basic Lookup, but as you have it setup you would need to wrap the elements of the lookup in some text extraction formulas to get the bits out that you're actually looking up with.

That said, you should validate the options people can type in and make that requirement go away because a) people will write stuff wrong, b) No point letting people type in Green Waistcoats if it's not in your table and c) you can then use a drop-down list to let people select.

Depending on how involved you want to get you can create the options dynamically from your table, or if you know it's a fixed array of options, just type it out in the Data validation options box.

There are a load of array and spill formulas that are quite new that will also let you setup all this stuff dynamically.

You could also pull your source data into Power Query and process it all, drop it into the Excel Data Model and present it however you need, with Slicers to select your products.

So yeah, 100 different ways to do this, the first option is the simplest, the last option is the most robust if you wanted to develop this into some sort of business application.

[paulrockliffe]

(I should have added setup a drop down for Country and a separate drop down for product rather than a single list of combinations)

[Abdul Abulbul Amir]

With the layout you have started with you can simply use an index match formula.

[paulrockliffe]

Abdul Abulbul Amir said:

With the layout you have started with you can simply use an index match formula.

You can't do it quite that simply because you won't get a match on "RED HATS INDIA" on any other cell. You need to extract the bits you want to match, correct the capitalisation and error-trap it because people will type it in wrong. Then you need to match across both columns and rows so you get the intersection.

Personally I would format it as a table, create a Pivot Table off that table and use that to deliver the rate to the user with a Slicer to choose the Country and the Product, but like I said there's loads of ways to do it and the best one depends on the wider context.

[nikaiyo2]

paulrockliffe said:

You can't do it quite that simply because you won't get a match on "RED HATS INDIA" on any other cell. You need to extract the bits you want to match, correct the capitalisation and error-trap it because people will type it in wrong. Then you need to match across both columns and rows so you get the intersection.

Personally I would format it as a table, create a Pivot Table off that table and use that to deliver the rate to the user with a Slicer to choose the Country and the Product, but like I said there's loads of ways to do it and the best one depends on the wider context.

Thanks all for the input, makes me realize how much I dont know

So I am clearly doing something wrong...



To my simple mind =LOOKUP(K5,A:A,E7) means look if K5 Matches A return E7... but it does not.

[Abdul Abulbul Amir]

=index($a$3:$g$7,match($k$5,$a$3:$a$7),match($j$5,$a$3:$g$3))

[nikaiyo2]

Abdul Abulbul Amir said:

=index($a$3:$g$7,match($k$5,$a$3:$a$7),match($j$5,$a$3:$g$3))

Thanks for that man, I posted this then caught covid so was not at work for 10 days, by then things had moved on. Then it was stock take time again and I remembered about this post.

That pointed me in the right direction, thank you for the help

[Jinx]

nikaiyo2 said:

Abdul Abulbul Amir said:

=index($a$3:$g$7,match($k$5,$a$3:$a$7),match($j$5,$a$3:$g$3))

Thanks for that man, I posted this then caught covid so was not at work for 10 days, by then things had moved on. Then it was stock take time again and I remembered about this post.

That pointed me in the right direction, thank you for the help

Just a quick note - might need XMATCH for an exact match if the array isn't ordered.