Match the same data from different rows

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

VLOOKUP to Left?
Use =VLOOKUP(A2,CHOOSE({1,2},$Z$1:$Z$99,$Y$1:$Y$99),2,False) to lookup Y values to left of Z values.
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 :)

Hi,

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

ABCDEFG
1Header1Header1CatTRUE
2carHeader2TrainTRUE
3trainHeader3CatFALSE
4cat
5dog
6
7Header2
8cat
9train
10
11Header3
12car
13dog
14train

<tbody>
</tbody>
Sheet1

Array Formulas
CellFormula
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)}

<tbody>
</tbody>
Entered with Ctrl+Shift+Enter. If entered correctly, Excel will surround with curly braces {}.
Note: Do not try and enter the {} manually yourself

<tbody>
</tbody>
 
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?

Hi,

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

ABCDEFG
1Header1Header1CatTRUE
2carHeader2TrainTRUE
3trainHeader3CatFALSE
4cat
5dog
6
7Header2
8cat
9train
10
11Header3
12car
13dog
14train

<colgroup><col style="width: 25pxpx"><col><col><col><col><col><col><col></colgroup><thead>
</thead><tbody>
</tbody>
Sheet1

Array Formulas
CellFormula
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)}

<thead>
</thead><tbody>
</tbody>
Entered with Ctrl+Shift+Enter. If entered correctly, Excel will surround with curly braces {}.
Note: Do not try and enter the {} manually yourself

<tbody>
</tbody>
 
Upvote 0
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?
Did you try either suggestion with more than 3 headers? ;)
 
Upvote 0

Forum statistics

Threads
1,213,546
Messages
6,114,256
Members
448,557
Latest member
richa mishra

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