How can I search a similar item - so stuck on this!

[nikki123]

So I have a rather large excel spreadsheet with a list of items and their codes, eg:

3652 Milk 1ltr
6743 Milk 2ltr
9287 Milk 3ltr
7264 Cheese 200g
7635 SC Cheese 120g

I am trying to create something whereby I type one of the numbers into a cell (say F2) and in the cell next to it (say F3) it shows the product that I have typed. I have managed this with VLOOKUP.

The issue I have is I want a formula that links to that first cell F2. I need in F4 for it then have an alternative product if the initial code is out of stock - so when I enter 3652 for Milk 1ltr it will say the alternative is 6743 Milk 2ltr or another brand of 1ltr.

I am useless with this and have been trying for ages to create something.... Does anyone have any ideas how to work this out and what formula or functions to use? Thanks in advance!

[6StringJazzer]

How do you determine what is a valid alternative product? To do it in Excel, you need to have some sort of a rule. If you were going to explain to a human how to do this with a list on paper, what would say? Imagine this is not a very bright human but someone who can follow directions exceptionally well.

Oh, and how do you tell if it's out of stock? It might be useful to attach your file.

[nikki123]

So I know if it is out of stock by using a different programme SAP, and all I will know is code 6432 is out of stock. So to find an alternative I was going to input it in the spreadsheet and hopefully find a solution that shows what else there is. (I will have to manually decide which are suitable substitutes, but for now an example would be a brie for a brie etc) I have tried to attach the file to see what has been done so far.

[samba_ravi]

In the Given example in attached file what is your result

[humdingaling]

work backwards
what would be suitable substitute for 6432?
systematically....how did you derive this substitute?

[nikki123]

I don't have a result so far which is the problem! I can't work out how and what formula to use..

I can make a list of suitable substitutes, they are basically just anything similar - so when I put in the out of stock code, I want it to spit back out another code that is similar (so I can use that in another system).

I don't think I'm making much sense here - sorry guys!

[:) Sixthsense :)]

Please go through the attached file and let us know whether the provided solution fulfills your expectation.

I have used couple of helper columns (for speeder processing) for arriving the data so it is better to have a look in the attached file

[nikki123]

Thanks that is really interesting. I take it the yellow cell is where I would enter the code which I am looking for a substitute, however it doesn't change anything in the table? Am I doing something wrong?!!

[:) Sixthsense :)]

Yes A2 is the input cell where you have to enter the SKU Number.

Make sure whether your calculation is set to AUTOMATIC or press F9 to initiate manual calculation.

[nikki123]

Oh yes, it wasn't set to automatic!! Thanks. That table is brilliant, its exactly what I had in mind.
So the formula you used, is that just finding similar volume wise? Can I edit it so it also finds, say others with the same brand but slightly different volume? So if 300g was out of stock, there's 250g or 400g?
Thanks for your help

[:) Sixthsense :)]

Please find the revised file which will show the available Product Vol when there is no exact matching Product Vol is found

[nikki123]

Thank you so much for your help, this is awesome really appreciate it.

[humdingaling]

the problem with what your asking if the data is not built that way
you are missing the additional descriptors of which you either have to keep in an excel file or get added into SAP

see attached a revision of what Sixth's solution
ps it information may not be 100% correct but its good enough to show you what needs to be done