Getting a value from two different columns

[Aditya Jadhav]

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

[#2]

@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

[#3]

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.

[#4]

@Aditya Jadhav 

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.

[#5]

@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?

[#6]

@Riny_van_Eekelen 

For the manual thing have attached a sheet which has the logic and details written in it.

[#7 · Accepted 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.

[#8]

Dear @Riny_van_Eekelen this works like a charm will try this out for the rest of the workbook.
Thanks a lot.

[#9]

@Riny_van_Eekelen

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.

[#10]

@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.