INDEX/MATCH Issues

patrick_oneal

New Member
Joined
Nov 14, 2016
Messages
11
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)}
 

Special-K99

Well-known Member
Joined
Nov 7, 2006
Messages
8,327
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
8,760
Office Version
2007
Platform
Windows
Check the following data

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

patrick_oneal

New Member
Joined
Nov 14, 2016
Messages
11
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,327
"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
8,760
Office Version
2007
Platform
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.
 

Forum statistics

Threads
1,081,702
Messages
5,360,743
Members
400,595
Latest member
T_Dubs

Some videos you may like

This Week's Hot Topics

  • VBA (Userform)
    Hi All, I just would like to know why my code isn't working. Here is my VBA code: [CODE=vba]Private Sub OKButton_Click() Dim i As Integer...
  • List box that changes fill color
    Hello, I have gone through so many pages trying to figure this out. I have a 2020 calendar that depending on the day needs to have a certain...
  • Remove duplicates and retain one. Cross-linked cases
    Hi all I ran out of google keywords to use and still couldn't find a reference how to achieve the results of a single count. It would be great if...
  • VBA Copy and Paste With Duplicates
    Hello All, I'm in need of some input. My VBA skills are sub-par at best. I've assembled this code from basic research and it works but is...
  • Macro
    is it possible for a macro to run if the active cell value is different to the value above it
  • IF DATE and TIME
    I currently use this to check if date has passed but i also need to set a time on it too. Is it possible? [CODE=vba]=IF(B:B>TODAY(),"Not...
Top