Nov 20 2021 01:24 AM
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
Nov 20 2021 01:47 AM
@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 |
Nov 20 2021 01:58 AM
Nov 20 2021 02:02 AM
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.
Nov 20 2021 02:09 AM
@Aditya Jadhav As @Sergei Baklan 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?
Nov 20 2021 02:32 AM
For the manual thing have attached a sheet which has the logic and details written in it.
Nov 20 2021 02:52 AM
Solution@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.
Nov 20 2021 03:04 AM
Jan 24 2022 09:53 PM
Have another problem now but the thing is that did not understand all the steps in power query to get the output. Would be helpful if you could guide me step by step so then that can be cleared entirely. attaching the file for ready reference.
Thanks in advance.
Jan 24 2022 10:17 PM
@Aditya Jadhav Better to open a new thread describing your problem, what file represents, what you need to do with it. And most importantly, what the end result should look like.
Nov 20 2021 02:52 AM
Solution@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.