Match the same data from different rows

P

pinnen

New Member
Joined
May 3, 2016
Messages
5
Hello

I have made an example of my problem below where I want to match data/ get to know if it exist in a group, separated by an empty cell. All "groups" are put in the same row and have a header. How can this be done by formulas?

How can I confirm if Header1 has cat?
How can I confirm if Header2 has train?
The data and the number of rows will change everytime I do a new match/ lookup:

Example:
Column A
Header1
car
train
cat
dog

Header2
cat
train

Header3
car
dog
train
 

Excel Facts

How can you turn a range sideways?
Copy the range. Select a blank cell. Right-click, Paste Special, then choose Transpose.
Sort by date Sort by votes
Hi,

Maybe not the most efficient solution but it works. Take a look t this and see if this helps:


Book1
ABCDEFG
1Header1Header1CatTRUE
2carHeader2TrainTRUE
3trainHeader3CatFALSE
4cat
5dog
6
7Header2
8cat
9train
10
11Header3
12car
13dog
14train
Sheet1
Cell Formulas
RangeFormula
G1{=ISNUMBER(MATCH(F1,OFFSET(INDIRECT(ADDRESS(MATCH(E1,$A$1:$A$30,0),1)),0,0,MIN(IF(INDIRECT(ADDRESS(MATCH(E1,$A$1:$A$30,0),1)&":A30")="",ROW(INDIRECT(ADDRESS(MATCH(E1,$A$1:$A$30,0),1)&":A30"))))-MATCH(E1,$A$1:$A$30,0),1),0)-1)}
Press CTRL+SHIFT+ENTER to enter array formulas.
 
Upvote 0
Thanks a lot! I will try this :)

jorismoerings said:
Hi,

Maybe not the most efficient solution but it works. Take a look t this and see if this helps:

ABCDEFG
Header1Header1Cat
carHeader2Train
trainHeader3Cat
cat
dog
Header2
cat
train
Header3
car
dog
train

<tbody>
[TD="align: center"]1[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: right"]TRUE[/TD]

[TD="align: center"]2[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: right"]TRUE[/TD]

[TD="align: center"]3[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: right"]FALSE[/TD]

[TD="align: center"]4[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]5[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]6[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]7[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]8[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]9[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]10[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]11[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]12[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]13[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]14[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

</tbody>
Sheet1​

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Array Formulas[TABLE="width: 100%"]
<tbody>[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]
[TH]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
[TR]
[TH="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]G1[/TH]
[TD="align: left"]{=ISNUMBER(MATCH(F1,OFFSET(INDIRECT(ADDRESS(MATCH(E1,$A$1:$A$30,0),1)),0,0,MIN(IF(INDIRECT(ADDRESS(MATCH(E1,$A$1:$A$30,0),1)&":A30")="",ROW(INDIRECT(ADDRESS(MATCH(E1,$A$1:$A$30,0),1)&":A30"))))-MATCH(E1,$A$1:$A$30,0),1),0)-1)}[/TD]
[/TR]
</tbody>[/TABLE]
Entered with Ctrl+Shift+Enter. If entered correctly, Excel will surround with curly braces {}.
Note: Do not try and enter the {} manually yourself[/TD]
[/TR]
</tbody>[/TABLE]
Click to expand...
 
Upvote 0
Here is a more compact version that also does not employ the volatile functions like OFFSET & INDIRECT and I think still does what you want.
This is also an array formula so should be entered without the {} but confirmed with Ctrl+Shift+Enter, not just Enter. If confirmed correctly, Excel will insert the {}. The formula can then be copied down.

Excel Workbook
ABCDEFG
1Header1Header1CatTRUE
2carHeader2TrainTRUE
3trainHeader3CatFALSE
4cat
5dog
6
7Header2
8cat
9train
10
11Header3
12car
13dog
14train
15
Check Group
 
Upvote 0
Hello again

I did not mention that I normally operate with way more than three headers. Is this solution only made for exactly three headers, or would it work no matter how many headers I use?

jorismoerings said:
Hi,

Maybe not the most efficient solution but it works. Take a look t this and see if this helps:

ABCDEFG
Header1Header1Cat
carHeader2Train
trainHeader3Cat
cat
dog
Header2
cat
train
Header3
car
dog
train

<colgroup><col style="width: 25pxpx"><col><col><col><col><col><col><col></colgroup><thead>
</thead><tbody>
[TD="align: center"]1[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: right"]TRUE[/TD]

[TD="align: center"]2[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: right"]TRUE[/TD]

[TD="align: center"]3[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: right"]FALSE[/TD]

[TD="align: center"]4[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]5[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]6[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]7[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]8[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]9[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]10[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]11[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]12[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]13[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]14[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

</tbody>
Sheet1​

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Array Formulas[TABLE="width: 100%"]
<thead>[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]
[TH="width: 10px"]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
</thead><tbody>[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]G1[/TH]
[TD="align: left"]{=ISNUMBER(MATCH(F1,OFFSET(INDIRECT(ADDRESS(MATCH(E1,$A$1:$A$30,0),1)),0,0,MIN(IF(INDIRECT(ADDRESS(MATCH(E1,$A$1:$A$30,0),1)&":A30")="",ROW(INDIRECT(ADDRESS(MATCH(E1,$A$1:$A$30,0),1)&":A30"))))-MATCH(E1,$A$1:$A$30,0),1),0)-1)}[/TD]
[/TR]
</tbody>[/TABLE]
Entered with Ctrl+Shift+Enter. If entered correctly, Excel will surround with curly braces {}.
Note: Do not try and enter the {} manually yourself[/TD]
[/TR]
</tbody>[/TABLE]
Click to expand...
 
Upvote 0
pinnen said:
I did not mention that I normally operate with way more than three headers. Is this solution only made for exactly three headers, or would it work no matter how many headers I use?
Click to expand...
Did you try either suggestion with more than 3 headers? ;)
 
Upvote 0
You must log in or register to reply here.

Similar threads

Ashe
  • Solved
Help matching multiple permissions in columns
Replies
6
Views
507
Fluff
Fluff
B
  • Question Question
Lookup match from other sheet
Replies
3
Views
176
Kaper
Kaper
J
  • Question Question
Power Query editor OR VBA to extract data from Grants.gov
Replies
3
Views
389
Jagadeshrao
J
R
  • Question Question
Fuzzy Match different product titles to find Duplicates but with-in same brand name
Replies
2
Views
80
RajK2005
R
J
  • Solved
Using Index/Match (or Something Else) to Return Row(s) of Data
Replies
4
Views
167
jbennett01
J

Forum statistics

Threads
1,224,823
Messages
6,181,178
Members
453,021
Latest member
Justyna P

Share this page

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
Back
Top