Forum Discussion
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 another range like 564-590 and give a value in front of that range which is required now I have many rows so the manual thing is out of question. Need help with the formulas as tried using the Index formula but the result is N/A.
Regards,
Aditya
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.
9 Replies
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.
Aditya Jadhav Can you please explain a bit more? For example, on the very first row, what would you like to return in the Geology column?
Range1 Range2 Range Geology 523.96 537.8 537.8-523.96 - Dear Riny_van_Eekelen,
The first geology column should get a text from the second geology column when the first range falls in between or is equal to the range in the next elevation column.Aditya Jadhav As SergeiBaklan already mentioned, most ranges from the first table overlap two or more "Geology codes" in the second. So, my question stands. What would you type in cell D2 if you had to do it manually?
