# 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

##### New Member
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.

• 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>

### Excel Facts

Using Function Arguments with nested formulas
If writing INDEX in Func. Arguments, type MATCH(. Use the mouse to click inside MATCH in the formula bar. Dialog switches to MATCH.

#### Fluff

##### MrExcel MVP, Moderator
Hi & welcome to MrExcel.
=INDEX(\$A\$2:\$A\$9,MATCH(C2,\$B\$2:\$B\$9,0))

#### BrandonBerner

##### New Member
Hi & welcome to MrExcel.
=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

##### MrExcel MVP, Moderator
You're welcome & thanks for the feedback

#### BrandonBerner

##### New Member
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

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!

Last edited by a moderator:

Replies
2
Views
140
Replies
1
Views
50
Replies
3
Views
302
Replies
0
Views
56
Replies
3
Views
50