Anyone an excel whizz and want to do a good deed for the world....

[touchingcloth]

Ctrl + f to find "type 1", then look in the cell four to the left of it x

[TrenterPercenter]

To get a single result for a row (if 'Type1' shows up twice, it'll give the first, I think) then:

=Index(A2:Q2, MATCH("Type1", A2:Q2,0)-4))

(for the first row in your mock-up)

It'll show #N/A if it can't find the label. If you don't like that you can fix it with:

IfError(Index(A2:Q2, MATCH("Type1", A2:Q2,0)-4)),"")

Thanks will try this tomorrow what is the "" at the end though?

[touchingcloth]

Thanks will try this tomorrow what is the "" at the end though?

It's for the iferror function (everything from "iferror(" to "-4))"). If that lots produces an error, don't display anything (though you could replace "" with "phimosis" to display the word phimosis).

[Ferris]

It's for the iferror function (everything from "iferror(" to "-4))"). If that lots produces an error, don't display anything (though you could replace "" with "phimosis" to display the word phimosis).

I use "fucked it" in my iferror functions, for my own amusement.

[TrenterPercenter]

Ctrl + f to find "type 1", then look in the cell four to the left of it x

Yes I have started doing this but we are talking about a dataset that is 1200x1000 data points.

[Sebastian Cobb]

See, the problem here is you're trying to bend excel into doing the job of an actual database.

[Ferris]

Do it in 2 functions.

Create 2 new columns. Use a countif on one column and have it return a value "yes" if the date you want is found in the row, so you have a column of "yes" and blank.
Then an if function in the next column, "if "yes" = [specific cell]". Job done.

[BlodwynPig]

See, the problem here is you're trying to bend excel into doing the job of an actual database.

Do Microsoft still do access?

[Sebastian Cobb]

Do Microsoft still do access?

Yes but it's probably better to muddle your way through something like sqlite (not much data), mysql or postgres (more data, multiple users) these days, especially as you could probably do it as a service under the AWS free tier.

[Zetetic]

PowerApps these days.

[TrenterPercenter]

Btw thanks for all the help with this I couldn't quite solve the entire problem with the formula but it really helped.