4 Columns (A1=SKU, B2=Name, C=Data Validation, D=Formula) HELP: If C=B1:B, Display A* (A*being the same row where match on B was found.

[BrandonBerner]

FIRST POST! Wehoo!
Excuse my lack of excel knowledge...Im trying haha.
Thank you for taking the time to open this, and give it a read

Objective:

3 ways to demonstrate what I am trying to achieve.

• Way #1 : D=(C1, B1:B), A1
• Way #2: Search B1:B for absolute match in C1. Once match found, find row of match, and display cell A*.
• Way #3: Find $C1 Product #5, In B1:B (found at B5), display A5 (since thats where Product #5 was found in B5) in $D1

Here are real formulas that I have tried.

• Way #1 : =IF(COUNTIF($B$1:$B,$C1), A*,"Not Found")

• I don't know how to formulate A*

....A...................B...............................C...............................D

SKU	Product Name	Data Validation	SKU (Repeat)
100	Product #1	Product #5	500
200	Product #2	Product #1	100
300	Product #3	Product #8	800
400	Product #4	Product #8	800
500	Product #5	Product #6	600
600	Product #6	Product #8	800
100	Product #7	Product #2	200
800	Product #8	Product #9	900

<tbody>
</tbody>

​

 

[Fluff]

Hi & welcome to MrExcel.
How about
=INDEX($A$2:$A$9,MATCH(C2,$B$2:$B$9,0))

 

[BrandonBerner]

Hi & welcome to MrExcel.
How about
=INDEX($A$2:$A$9,MATCH(C2,$B$2:$B$9,0))

YOU ROCK!!!!!!!
The formula worked perfectly!!!

I made a minor tweak since I did not like seeing the #N/A (If false)
So I used...
=IFERROR(INDEX($A$2:$A$9,MATCH(C2,$B$2:$B$9,0)),"")


Thank You!!

 

[Fluff]

You're welcome & thanks for the feedback

 

[BrandonBerner]

For those that come across this post, and are a beginner, here is how I understood this formula, maybe it will help you...

=IFERROR(INDEX($A$2:$A$9,MATCH(C2,$B$2:$B$9,0)),"")

A2:A9 = The Source, or the "thing" you want to be displayed in another cell.
C2 = Instance #1
B2:B9= The Column where the same Instance #1 is.


Why did I find this formula so useful?

In my case, I have a huge database of Product Names, SKUs, Price, and Inventory Count.

I wanted to make a user-friendly way for my co-workers to quickly sort through all of this information do decrease customer wait times.

Before this formula, my co-workers had to manually sort through a list of thousands of products to find the price of an item for a customer. It would take them sometimes over 10 minutes to find the price of a product; which is outrageous. (I personally just used the CTRL+F to find the info in the database, but even that was toooo challenging for my co-workers -_-

Now, all they do is select the item from a drop down menu, or begin to type the product name and have it auto-complete EVERYTHING they need when talking to a customer.

Next, once they select the product from the dropdown, the formula=IFERROR(INDEX($A$2:$A$9,MATCH(C2,$B$2:$B$9,0)),"") pulls only the relative information for that specific product. All this happens in less than a few seconds!



Here is what my DATABASE looks like
(I put this on Sheet2, and locked it so my co-workers don't delete it (like the have in the past).

A BC D
Product #1 SKU #1Price #1 Inventory #1
Product #2 SKU #2 Price #2 Inventory #2
Product #3 SKU #3 Price #3 Inventory #3
Product #4 SKU #4Price #4 Inventory #4
Product #5 SKU #5 Price #5 Inventory #5



Here is what my Co-Workers see:
(I put this on Sheet1, and locked every cell, EXCEPT the drop-down menu).

A B C D
Select A Product SKU Price Inventory
[Drop-Down Menu] [Auto-Fill] [Auto-Fill] [Auto-Fill]

How I formatted Sheet1...

1. Select A Product:[Drop Down Menu]
- Drop-Down was created using Data Validation, and selecting the column:Sheet2!A1:A
2. SKU [Auto-Fill]:[Auto-Completes - based on Drop-Down Menu selection]

- Formula:=IFERROR(INDEX(Sheet2!B1:B,MATCH(A2,Sheet2!A1:A,0)),"")

- Finds the match from onSheet1 (column of product names), and Sheet2 (the product selected using the Drop-Down), then displays theSKU for that specific product.

3. Price[Auto-Fill]:[Auto-Completes - based on Drop Down Menu selection]

- Formula: =IFERROR(INDEX(Sheet2!C1:C,MATCH(A2,Sheet2!A1:A,0)),"")

- Finds the match from on Sheet1 (column of product names), andSheet2(the product selected using the Drop-Down), then displays the Price for that specific product.

4. Inventory [Auto-Fill]: [Auto-Completes - based on Drop Down Menu selection]

- Formula:=IFERROR(INDEX(Sheet2!D1:D,MATCH(A2,Sheet2!A1:A,0)),"")

- Finds the match from onSheet1 (column of product names), andSheet2 (the product selected using the Drop-Down), then displays the Inventoryfor that specific product.


I hope this mess of a post find use to someone out there haha!