Microsoft Office Forums

Go Back   Microsoft Office Forums > >

Reply
 
Thread Tools Display Modes
  #1  
Old 05-05-2023, 01:58 PM
khaughey khaughey is offline Reverse Lookup from Table Windows 10 Reverse Lookup from Table Office 2021
Novice
Reverse Lookup from Table
 
Join Date: May 2023
Posts: 2
khaughey is on a distinguished road
Default 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
Reply With Quote
  #2  
Old 05-06-2023, 10:55 AM
p45cal's Avatar
p45cal p45cal is offline Reverse Lookup from Table Windows 10 Reverse Lookup from Table Office 2019
Expert
 
Join Date: Apr 2014
Posts: 863
p45cal has a brilliant futurep45cal has a brilliant futurep45cal has a brilliant futurep45cal has a brilliant futurep45cal has a brilliant futurep45cal has a brilliant futurep45cal has a brilliant futurep45cal has a brilliant futurep45cal has a brilliant futurep45cal has a brilliant futurep45cal has a brilliant future
Default

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)
Note that B4:G18 is the table EXCLUDING the very top row with the label V.
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?
Attached Files
File Type: xlsx msofficeforums50827_R.xlsx (12.7 KB, 1 views)
Reply With Quote
  #3  
Old 05-06-2023, 12:10 PM
khaughey khaughey is offline Reverse Lookup from Table Windows 10 Reverse Lookup from Table Office 2021
Novice
Reverse Lookup from Table
 
Join Date: May 2023
Posts: 2
khaughey is on a distinguished road
Default

Thanks, I need to study this solution!
Reply With Quote
Reply

Thread Tools
Display Modes


Similar Threads
Thread Thread Starter Forum Replies Last Post
Reverse Lookup from Table PASTE FORMULA from lookup table ChrisOK Excel Programming 19 04-22-2020 05:01 PM
Reverse Lookup from Table 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
Reverse Lookup from Table Lookup table in word ElfegoBaca Word 1 10-15-2014 06:34 PM

Other Forums: Access Forums

All times are GMT -7. The time now is 04:18 AM.


Powered by vBulletin® Version 3.8.11
Copyright ©2000 - 2024, vBulletin Solutions Inc.
Search Engine Optimisation provided by DragonByte SEO (Lite) - vBulletin Mods & Addons Copyright © 2024 DragonByte Technologies Ltd.
MSOfficeForums.com is not affiliated with Microsoft