INDEX MATCH to return column header based on empty value in row

NikToo

Board Regular
Joined
Sep 24, 2015
Messages
53
Office Version
  1. 365
I feel I'm almost there with this but not quite getting it right. I have a list of product numbers, dates across the columns, and a number in the data. What I want to do is find the product number, find the first non-blank cell in the data and return the date in the column header. I've been staring at Index/Match and just going round in a circle now.

Basically it'll give me the date the product was first sold.

Thanks in advance
 

Some videos you may like

Excel Facts

Difference between two dates
Secret function! Use =DATEDIF(A2,B2,"Y")&" years"&=DATEDIF(A2,B2,"YM")&" months"&=DATEDIF(A2,B2,"MD")&" days"

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
55,358
Office Version
  1. 365
Platform
  1. Windows
What version of Excel are you using?
I suggest that you update your Account details (or click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)

Also can you please post some sample data.

MrExcel has a tool called “XL2BB” that lets you post samples of your data that will allow us to copy/paste it to our Excel spreadsheets, so we can work with the same copy of data that you are. Instructions on using this tool can be found here: XL2BB Add-in

Note that there is also a "Test Here” forum on this board. This is a place where you can test using this tool (or any other posting techniques that you want to test) before trying to use those tools in your actual posts.
 

NikToo

Board Regular
Joined
Sep 24, 2015
Messages
53
Office Version
  1. 365
I can't install add-ins as it's against company policy. Same with sample data, I'd have to cleanse it first as it's sensitive.
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
55,358
Office Version
  1. 365
Platform
  1. Windows
The sample can be anything that represents your layout & type of values.

Please also answer my first question. Thanks
 

NikToo

Board Regular
Joined
Sep 24, 2015
Messages
53
Office Version
  1. 365

ADVERTISEMENT

ABCDE
1Code
16/02/2021​
17/02/2021​
18/02/2021​
19/02/2021​
2111
51.2​
38.4​
128​
51.2​
3222
12​
0​
4333
144​
176​
128​
240​
5444
100.8​
91.2​
52.8​
76.8​
6555
42​
14​
14​
42​

Sorted the version (365). So basically I want to find the date 18/02/2021 above the 12 for the row marked 222.
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
55,358
Office Version
  1. 365
Platform
  1. Windows
Thanks for that, how about
+Fluff 1.xlsm
ABCDEFGHI
1Code16/02/202117/02/202118/02/202119/02/2021
211151.238.412851.222218/02/2021
322212055517/02/2021
433314417612824033316/02/2021
5444100.891.252.876.8
6555141442
7
Summary
Cell Formulas
RangeFormula
I2:I4I2=INDEX(FILTER($B$1:$E$1,FILTER($B$2:$E$6,$A$2:$A$6=H2)>0),1)
 

gigidica

New Member
Joined
Oct 14, 2014
Messages
10

ADVERTISEMENT

You can also use the INDEX MATCH:
=INDEX(A1:E6,MATCH(H2,A1:A6,0),MATCH(I2,A1:E1,0))
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
55,358
Office Version
  1. 365
Platform
  1. Windows
@gigidica
How is that going to find the first non blank cell in the row & return the header?
 

NikToo

Board Regular
Joined
Sep 24, 2015
Messages
53
Office Version
  1. 365
FILTER isn't a supported function. I might need to try it as a VBA loop.
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
55,358
Office Version
  1. 365
Platform
  1. Windows
What do you mean by "FILTER isn't a supported function"?
 

Watch MrExcel Video

Forum statistics

Threads
1,127,204
Messages
5,623,352
Members
415,969
Latest member
Rey99

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