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
Joined
Mar 16, 2019
Messages
15
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 :biggrin:

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
SKUProduct NameData ValidationSKU (Repeat)
100Product #1 Product #5500
200Product #2Product #1 100
300Product #3Product #8800
400Product #4Product #8800
500Product #5Product #6600
600Product #6Product #8800
100Product #7Product #2200
800Product #8Product #9900





<tbody>
</tbody>

 

Some videos you may like

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
Joined
Jun 12, 2014
Messages
46,077
Office Version
  1. 365
Platform
  1. Windows
Hi & welcome to MrExcel.
How about
=INDEX($A$2:$A$9,MATCH(C2,$B$2:$B$9,0))
 

BrandonBerner

New Member
Joined
Mar 16, 2019
Messages
15
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!! :biggrin:
 

BrandonBerner

New Member
Joined
Mar 16, 2019
Messages
15
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!
 
Last edited by a moderator:

Watch MrExcel Video

Forum statistics

Threads
1,108,632
Messages
5,523,994
Members
409,554
Latest member
denistrevisan

This Week's Hot Topics

Top