SOLVED

Getting a value from two different columns

Contributor

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

7 Replies

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

Range1Range2RangeGeology
523.96537.8537.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 

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

@Riny_van_Eekelen 

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

 

best response confirmed by Aditya Jadhav (Contributor)
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.

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