Lookup and tables

[ANTBOH]

Orientation of collector
Tilt of collector South SE/SW E/W NE/NW North
Horizontal 933 933 933 933 933
30 degrees 1042 997 886 762 709
45 degrees 1023 968 829 666 621
60 degrees 960 900 753 580 485
Vertical 724 684 565 427 360

I need to make it so Excel will Lookup a piece of data in the above table
when someone writes in the Tilt of the collector in one cell, and the
orientation in another.

IE Tilt of collector: 30
Orientation of collector: E
Answer (generated by formula) = 886

Can anyone help me with this? I would use IF(AND(OR but that means i would
exceed my nesting limit

Thanks!

 

[Paul C]

Change your table a little and this will be easy.

A B C D E F G H
I J
1 Orientation of collector
2 Tilt of collector S SE SW E W NE NW N
3 0 933 933 933 933 933 933 933 933
4 30 1042 997 997 886 886 762 762 709
5 45 1023 968 968 829 829 666 666 621
6 60 960 900 900 753 753 580 580 485
7 90 724 684 684 565 565 427 427 360
8
9
10 IE Tilt of collector: 30
11 Orientation of collector: E
12 Answer: 886

Assuming A2 is the Cell with "Tilt of Collector" in it, 30 is entered in C10
and E in C11 the formula for you answer in C12 would be.

=VLOOKUP(C10,A3:I7,MATCH(C11,A2:I2,0),TRUE)

Using Vlookup on C10 finds the correct row and the Match finds the correct
column.


--If this helps, please remember to click yes.

 

[ANTBOH]

Hi Paul,

Thanks so much for having a go at this

Could you just double check your formula as I am sure you are almost there
but, in this example, instead of coming up with 886, it comes up with 45, as
in it is finding the tilt of the collector.

Should the array for the MATCH be increased?

Thanks again

Ant

 

[T. Valko]

Your data table isn't conducive to an easy solution. Can you redesign it?

It would be much easier (simple!) if you could use separate columns for each
direction. Instead of combining SE/SW, E/W and NE/NW put each of those in
its own column:

South | SE | SW | E | W | NE | NW | North

 

[ANTBOH]

Actually I was wrong, you cracked it, thanks very much!!!!