#1
|
|||
|
|||
Reverse Lookup from Table
Hello, I have been stumped on this one all day. I have a table V values across the top columns, Y values down the side rows, R values populated in the table. If I am given V and given R, how can I go to the given V column, pick out the closest R value and return the associated Y value? I have attached the spreadsheet and look forward to any suggested solutions. Thank you in advance for your time and expertise! Here is the spreadsheet: Microsoft OneDrive - Access files anywhere. Create docs with free Office Online.R.xlsx |
#2
|
||||
|
||||
The attached has a result in cell L5 which does NOT give you the closest R but a value of R interpolated (linear regression) from the 2 closest values in the appropriate column to the given R.
The formula in L5 is: Code:
=ReverseY(B4:G18,J3,J4) You get hints about what belongs where in the formula: 2023-05-06_184759.jpg The formula is a lambda formula given the name ReverseY in the Name Manager. There's a cell O5 showing that lambda formula, but it's there only for information, it's not needed. ps. if you had the value of 583 for a V of 40, that's exactly midway between 580 and 586, what would the 'closest' Y be? |
#3
|
|||
|
|||
Thanks, I need to study this solution!
|
|
Similar Threads | ||||
Thread | Thread Starter | Forum | Replies | Last Post |
PASTE FORMULA from lookup table | ChrisOK | Excel Programming | 19 | 04-22-2020 05:01 PM |
Table of Contents appears in reverse when inserted into document | chiron34 | Word | 1 | 05-19-2017 09:51 PM |
Complex 3-inputs lookup table | Mr.Onion | Excel | 2 | 12-06-2016 03:12 PM |
LOOKUP - Complex lookup with 2 lookups in 1 cell | sglandon | Excel | 6 | 05-05-2016 09:44 AM |
Lookup table in word | ElfegoBaca | Word | 1 | 10-15-2014 06:34 PM |