5 Replies Latest reply on Jun 27, 2018 7:48 AM by Donald Olsen

# How to build a Hlookup style calculation

Hello,

How would I recreate an excel style Hlookup type calculation on my table?

In my mock up below I want to use the column "First OFS Year (float)" to drive the hlookup for the column that has the same "Full Date" then give me the value of that intersect.

So because the row ""First OFS Year (float)" = 2023

I want the value in the column "Full Date" that intersects in this case '166'

The Excel formula i'm trying to reproduce is this =IFERROR(HLOOKUP(\$E7,\$F\$2:\$Y7,\$AA7,0)

• ###### 1. Re: How to build a Hlookup style calculation

HI Donald,

I am not sure how your use this calculated results, but here is hints.

Thanks,

Shin

1 of 1 people found this helpful
• ###### 2. Re: How to build a Hlookup style calculation

I have this open.  I'm trying to figure out the data here.

I think you've over-complicated this with all the table calcs.  Take a look at [First Non-zero mdu(copy)] in dimensions.  I used LOD to do what I think you are doing with the table calc in your version of the same.

If we get all the table calcs out of there and use LOD, this will simplify what's going on in here.  Then I can start to address what you actually want to do here.

1 of 1 people found this helpful
• ###### 3. Re: How to build a Hlookup style calculation

Ok first huge thank you to both.

I think i need a combination of both fixes.

Joe I made one change to the LOD calculation you had so the 4 different components SFU,SBU,MDU and MTU where considered as one

{ FIXED [REGION],[SUB_REGION],[CLLI] : MIN( if [MDU] <> 0 or [MTU] <> 0 or [SBU] <> 0 or  [SFU] <> 0 then [FULL DATE] END ) }

Which is much cleaner.

Now I think if i can use Shinichiro did with the new "first non-zero value AUI" I should have what i need.

Im a novice honestly only started using Tableau about a month ago so thank you for staying with me.

So I'm now getting a "cannot mix aggregate and non aggregate arguments in the "SFU 1styr example"

• ###### 4. Re: How to build a Hlookup style calculation

Ok I'm getting there again thank you for the clues.

• ###### 5. Re: How to build a Hlookup style calculation

Just to close the loop and once again thanks for the help. I got the formula as I needed it.

.

Now I get the first years component only in a column