Forum Discussion
Solved
Getting a value from two different columns
Hi, Want to get values after matching the two ranges from two different columns and cannot get a solution. For eg, Have a range of 573.2-584.5 now this should be looked into another column which has...
Aditya Jadhav Can see what you want to achieve though I see a slight problem. Your Geology list has 17 different ranges for code RL. To overcome that I renamed them to RL01 to RL17. Then I played around a bit with Power Query, without attempting to optimize the process. Ultimately, it gives you the Geo-codes for the lowest and highest values in the range. Perhaps this will help you in the other Geo-codes that fall between these two.
See attached.
You may create couple of helper columns with Start and End of Elevation
Start
=--RIGHT( G2, LEN( G2) - FIND( "-", G2) )
End
=--LEFT( G2, FIND( "-", G2) - 1 )and lookup period as
=IFNA( INDEX( $H$2:$H$74, MATCH( 1, INDEX( ($A2 >= $I$2:$I$74 )*( $B2 <= $J$2:$J$74 ), 0 ), 0 ) ), "no such")However, majority of ranges are over few periods.