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!

 

[temens]

I may be able to help, but I need you to answer a couple of questions:

1. Does the alternate item only show up if the requested item is out of stock, or does it always show the alternate item?

2. Where are alternate items listed? Or how is an alternate item determined for each item entered? (If each item has an alternate already designated somewhere, then it would be fairly easy to retrieve that info)

Good luck! I'll see if I hear back from you.