Excel if formula

[Melissa McDowell]

Hi all,
So I need to create formulae that will check the entire worksheet. So for example I need to be able to input all my product names and then have the stock codes generate automatically. So if "blue spotted dog" appears somewhere in the column, I want "BSD" to appear in the next column.

[#2 · Accepted solution]

Hi Melissa,

Sounds a bit like you need a VLOOKUP formula, but when you say check the entire worksheet do you mean check a column in a worksheet?

Can you attach a small example file of what you mean please.

Thanks

[#3]

Hi Melissa!
All you need to do is to enter the "Product Name" and "Stock Codes" in two columns respectively. Make sure that the product name should be unique.
then you can use "Vlookup" function to see the same result any where in your workbook.
thank you.

[#4]

It's a little hard to explain. I'm trying to get together an inventory of book titles and their product codes. So when I need to place several orders at once, I need to be able to copy and paste the titles into column P, and have the product codes appear in column M. The information needed is in cells C20-D25 (ideally I would like to keep this inventory in another sheet if I can). I have attached a pretty dodgy example. Please help!!!

[#5]

Hi Melissa

Would the attached file work?

It uses an INDEX MATCH formula to bring back the Stock code based on the book name

The index part references the column of MPNs (which I've setup in a table called tblInventory)

=INDEX(   tblInventory[MPN],

The Match part looks up  the book title and finds the row its on in the Book Name column of tblInventory

MATCH( [@[Book Title]],  tblInventory[Book Name], 0),0)

So when it finds a match it brings back the corresponding MPN for that row / book title.

[#6]

Thank you so so much.