Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Lookup and tables
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! |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Lookup and tables
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" wrote: 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! |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Lookup and tables
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 "Paul C" wrote: 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" wrote: 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! |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Lookup and tables
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 -- Biff Microsoft Excel MVP "ANTBOH" wrote in message ... 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! |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Lookup and tables
Actually I was wrong, you cracked it, thanks very much!!!!
"ANTBOH" wrote: 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 "Paul C" wrote: 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" wrote: 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! |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Lookup in Tables | Excel Worksheet Functions | |||
Lookup Tables | Excel Worksheet Functions | |||
lookup tables | Excel Worksheet Functions | |||
Lookup tables | Excel Worksheet Functions | |||
lookup tables | Excel Worksheet Functions |