HLOOKUP then check if flagged as result then return value

[jwalkerday]

Sheet1
[TABLE="width: 191"]
<tbody>[TR]
[TD][/TD]
[TD]2019
[/TD]
[TD]2020
[/TD]
[/TR]
[TR]
[TD]1234
[/TD]
[TD]54
[/TD]
[TD]66
[/TD]
[/TR]
[TR]
[TD]5678
[/TD]
[TD]14
[/TD]
[TD]44
[/TD]
[/TR]
</tbody>[/TABLE]

Sheet2
[TABLE="width: 287"]
<tbody>[TR]
[TD][/TD]
[TD]2019
[/TD]
[TD]2019
[/TD]
[TD]2019
[/TD]
[/TR]
[TR]
[TD]Result Flag
[/TD]
[TD][/TD]
[TD][/TD]
[TD]Result
[/TD]
[/TR]
[TR]
[TD]1234
[/TD]
[TD]54
[/TD]
[TD]51
[/TD]
[TD]77
[/TD]
[/TR]
[TR]
[TD]5678
[/TD]
[TD]14
[/TD]
[TD]28
[/TD]
[TD]101
[/TD]
[/TR]
</tbody>[/TABLE]

=HLOOKUP(B$1,Sheet2!$A$1:$Y$4,MATCH($A2,Sheet2!$A$1:$A$4,FALSE),FALSE)

Hi,
Above you can see a shortened version of my 2 sheets. The hlookup you can see highlighted in blue returns the first value that matches the year And the reference code (1234).

However I want the value underneath the result flag(77). I've looked into a few different functions (IF, MATCH,INDEX) but been unable to successfully Return the result value.

So, I need to check for the year; then check if the result flag is present and then return the value.

Can anyone help?

 

[Eric W]

Welcome to the forum.

Either:

=SUMPRODUCT(Sheet2!$B$3:$D$4*(Sheet2!$B$1:$D$1=B$1)*(Sheet2!$B$2:$D$2="Result")*(Sheet2!$A$3:$A$4=$A2))

or

=INDEX(Sheet2!$B$3:$D$4,MATCH($A2,Sheet2!$A$3:$A$4,0),MATCH(B$1&"|Result",Sheet2!$B$1:$D$1&"|"&Sheet2!$B$2:$D$2,0))
with Control+Shift+Enter.

 

[jwalkerday]

Brilliant thanks.

I used-
=SUMPRODUCT(Sheet2!$B$3:$D$4*(Sheet2!$B$1:$D$1=B$1)*(Sheet2!$B$2:$D$2="Result")*(Sheet2!$A$3:$A$4=$A2))

 

[Eric W]

Glad to help!

Keep in mind that there is a difference between the formulas. If you have multiple matches for some reason, the SUMPRODUCT will return the sum of the matches. The INDEX/MATCH/MATCH will return the first match, and it will work for non-numeric values too.

 

[jwalkerday]

Ah that's very useful thanks.


In this case there are hundreds of predictions but only ever one result and it's run as a data extract from a DB which checks to ensure there is only one result per year. And they will always be numbers so it's working perfectly on a large dataset.


I like the idea of the index with the CSE {} however I'd be wary of using it on a multiuser spreadsheet. I'll do some more reading on the impact of using CSE and what happens if someone else edits the formula without realising they need to use CSE.