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

[NikToo]

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

 

[Fluff]

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]

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]

The sample can be anything that represents your layout & type of values.

Please also answer my first question. Thanks

 

[NikToo]

	A	B	C	D	E
1	Code	16/02/2021​	17/02/2021​	18/02/2021​	19/02/2021​
2	111	51.2​	38.4​	128​	51.2​
3	222	​		12​	0​
4	333	144​	176​	128​	240​
5	444	100.8​	91.2​	52.8​	76.8​
6	555	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]

Thanks for that, how about

+Fluff 1.xlsm
	A	B	C	D	E	F	G	H	I
1	Code	16/02/2021	17/02/2021	18/02/2021	19/02/2021
2	111	51.2	38.4	128	51.2			222	18/02/2021
3	222			12	0			555	17/02/2021
4	333	144	176	128	240			333	16/02/2021
5	444	100.8	91.2	52.8	76.8
6	555		14	14	42
7
Summary

Cell Formulas
Range	Formula
I2:I4	I2	=INDEX(FILTER($B$1:$E$1,FILTER($B$2:$E$6,$A$2:$A$6=H2)>0),1)

 

[gigidica]

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

 

[Fluff]

@gigidica
How is that going to find the first non blank cell in the row & return the header?

 

[NikToo]

FILTER isn't a supported function. I might need to try it as a VBA loop.

 

[Fluff]

What do you mean by "FILTER isn't a supported function"?