INDEX/MATCH Issues

patrick_oneal

New Member
Joined
Nov 14, 2016
Messages
12
Name
Type
Clean Data
DOGS
CATS
FRANK
-DOG
DOG
FRANK
JEFF
JEFF
-CAT
CAT
AMY
KELLY
KELLY
-CAT
CAT
AMY
-DOG
DOG

<tbody>
</tbody>
I'm trying to grasp index, match to work on a spread sheet. All of the other questions I find are not helping all that much.

Cells E/D (DOGS,CATS) is what I want the output to look like, I want to match all data in the C column to cell E1 (DOGS), and display the name.
Code:
{=INDEX($A$2:$C$5,MATCH($E$1,$C2:$C$5,0),1)}
 

Some videos you may like

Excel Facts

Format cells as currency
Select range and press Ctrl+Shift+4 to format cells as currency. (Shift 4 is the $ sign).

Special-K99

Well-known Member
Joined
Nov 7, 2006
Messages
8,350
1. Make your data consistent.
In the output change the column heading to DOG and CAT, not DOGS and CATS

2. in H2
=IFERROR(INDEX($A$2:$A$5,AGGREGATE(15,6,ROW($A$2:$A$5)/(($C$2:$C$5=E$1)),ROWS(A$2:A2))-(2-1),1),"")
copy across and down for as many rows as you have names in column A (in this case down to row 5)
 

DanteAmor

Well-known Member
Joined
Dec 3, 2018
Messages
12,594
Office Version
  1. 2007
Platform
  1. Windows
Check the following data

Excel Workbook
ABCDEF
1NameTypeClean DataDOGCAT
2FRANK-DOGDOGFRANKJEFF
3JEFF-CATCATAMYKELLY
4KELLY-CATCAT
5AMY-DOGDOG
6
7
8
9
10
Hoja4
 

patrick_oneal

New Member
Joined
Nov 14, 2016
Messages
12
I utilized this code and was successful, thank you. What is i wanted to add another column for color and look for items that matched say “DOG & Brown?”
 

Special-K99

Well-known Member
Joined
Nov 7, 2006
Messages
8,350
"I utlilized this code"
To whom are you referring, you have two answers but used only one solution?

Wiht "Brown" in column D
=IFERROR(INDEX($A$2:$A$5,AGGREGATE(15,6,ROW($A$2:$A$5)/(($C$2:$C$5=E$1)*(D$2:D$5="Brown"),ROWS(A$2:A2))-(2-1),1),"")
 

DanteAmor

Well-known Member
Joined
Dec 3, 2018
Messages
12,594
Office Version
  1. 2007
Platform
  1. Windows
I utilized this code and was successful, thank you.

You're welcome.

If you present your idea of ​​how you have your data and how you want the results we could establish a formula.
Maybe, it would be better to use a pivot table.
 

Watch MrExcel Video

Forum statistics

Threads
1,122,232
Messages
5,594,956
Members
413,954
Latest member
mrsandy

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top