A 3 dimensional table question

I have tried to make this work with index- match and vlookup by removing some data, dropping the lateral run, but still having issues. I guess the main issue is I need to return the top row values when looking up something in the table- instead of the other way around

Any help appreciated

Please post in your workbook the values your looking for, and the desired anwer (2 examples)

Then i will look if i can solve your problem.

Great- I have reuploaded the workbook but at version 2 where i included what i have been trying to do outside of a VBA solution. Ideally i would want a VBA solution to work on the desired sheet but the acceptable sheet has what i am trying to do with equations

Yellow highlight illustrates the issue using equations


Much appreciated!

Sorry misposted

I erased the post because I was not clear in what i responded with

Here is an example of what I am looking for

For example:

Height=100
Lateral Run=30
Value-7000 (this is not calculated)- it's user defined

program goes to that row,->finds the next highest value which is 7209

Then looks up to Row 3

And returns 18

Maybe this way?

Hi- Great job-wow. I am looking to see if there is a way to make the Fan yes/no work. I would try to change the equation but can you please try?

tigeravatar- sorry about that

Replying to tigeravatar first- I transposed the equation to the working spreadsheet and I get the correct values when I look at the fx function in excel but the Result is #value!- I verfied all data is formatted to a number.

Any ideas?

To tiger avatar- Attaching what I am seeing- I tried it 2 ways- directly on same sheet and like you did

to tiger avatar- on your file, I noticed a {= in the formula beginning and a closing } that shows up when I click on the Value calculation (long equation). If I enter the equation and change nothing- it results in #N/A.

to oeldere - Same issue with yours ( I removed the absolute references to copy it over)- Column is #N/A- but row works

to oeldere I have my mistake figured out- wow silly error

to tiger avatar- let me first fix what oeldere has working in my workbook and then will see why yours has issues (likely same cause)

Many thanks!- will repost if I still have issues

WOW!- thank you very much- I dissected the equation and it's parts and kept gluing them togther. Break for me- i will come back and get it to work- again thanks so much

I am looking at mike's suggestion on how to include Fan yes/no. The Yes/no was not in what tigeravater sent- do i have to redo the entire equation with the fan index statement or is that possible since it is in a row vs column? I am trying to understand the tigeravatar construct

Yes need help- can't figure where to put the check for fan or not

Many thanks- I understand (I think) the logic now- very simple but powerful. I appreciate the patience- hopefully I have learned enough to do it myself next time.

Very appreciative and grateful tigeravatar