I want to find a value in a range of cells and then show a value from a different column on the same row

P

polvalt

New Member
Joined
Jun 8, 2022
Messages
2
Office Version
  1. 365
Platform
  1. MacOS
I'm such a noob, I don't even know how to ask this question simply...

I typed what I'd like to do directly into the mini sheet below...

Test.xlsx
ABCDEFGHIJ
1
2
3
4if I put the number 20 in this cell (B4)
5I want this cell to show what value is in column J on the same row as the closest number to 20 in the range B8:B34. (Which would be the value in J25)
6
7
88.63327937.3382874067.510952997.683618587.856284168.028949758.201615348.374280928.546946511
99.493843138.0697666648.259643538.449520398.639397258.829274119.019150989.209027849.39890471.1
1010.35390838.8008220729.007900249.21497849.422056579.629134749.836212910.043291110.25036921.2
1111.21347589.5314544599.755723989.9799934910.20426310.428532510.65280210.877071611.10134111.3
1212.072546610.2616646510.503115610.744566510.986017511.227468411.468919311.710370211.95182121.4
1312.931121710.9914534711.250075911.508698311.767320812.025943212.284565612.543188112.80181051.5
1413.789202111.7208217511.996605812.272389812.548173912.823957913.09974213.37552613.651311.6
1514.646788612.449770312.742706113.035641813.328577613.621513413.914449214.207384914.50032071.7
1615.503882313.1782999513.488377613.798455214.108532914.418610514.728688215.038765815.34884351.8
1716.360484113.9064115114.233621214.560830914.888040615.215250215.542459915.869669616.19687931.9
1817.216595114.6341058114.978437715.322769615.667101516.011433416.355765316.700097217.04442912
1918.072216115.3613836615.72282816.084272316.445716616.807160917.168605317.530049617.89149392.1
2018.927348116.0882458716.466792816.845339817.223886817.602433717.980980718.359527618.73807462.2
2119.781992116.8146932517.210333117.605972918.001612818.397252618.792892519.188532319.58417212.3
2220.63614917.5407266317.953449618.366172618.778895619.191618619.604341520.017064520.42978752.4
2321.489819818.2663468118.696143219.125939619.55573619.985532420.415328820.845125221.27492162.5
2422.343005418.991554619.438414719.885274820.332134920.77899521.225855121.672715322.11957542.6
2523.195706819.7163508120.180264920.644179121.108093221.572007422.035921522.499835622.96374982.7
2624.04792520.4407362420.921694721.402653221.883611722.364570222.845528723.326487223.80744572.8
2724.899660821.164711721.662704922.160698122.658691423.156684623.654677824.15267124.65066422.9
2825.750915321.88827822.403296322.918314623.433332923.948351224.463369524.978387825.49340613
2926.601689322.6114359323.143469723.675503524.207537324.739571125.271604925.803638626.33567243.1
3027.451983923.3341862923.88322624.432265624.981305325.53034526.079384726.628424427.1774643.2
3128.301799924.056529924.622565925.188601925.754637926.320673926.886709927.452745928.01878193.3
3229.151138324.7784675325.361490325.944513126.527535827.110558627.693581428.276604128.85962693.4
333025.526.126.727.327.928.529.129.73.5
3430.84838626.2211280926.838095827.455063528.072031328.68899929.305966729.922934430.53990213.6
Sheet1
 

Excel Facts

Excel motto
Not everything I do at work revolves around Excel. Only the fun parts.
Sort by date Sort by votes
Welcome to the MrExcel forum!

Try:

Excel Formula: 
=INDEX(J8:J34,MATCH(MIN(ABS(B8:B34-B4)),ABS(B8:B34-B4),0))
 
Upvote 0
Eric W said:
Welcome to the MrExcel forum!

Try:

Excel Formula: 
=INDEX(J8:J34,MATCH(MIN(ABS(B8:B34-B4)),ABS(B8:B34-B4),0))
Click to expand...
You are a genius and have saved me potentially hundreds of minutes!!!
 
Upvote 0
You must log in or register to reply here.

Similar threads

J
  • Solved
Combine rows based on previous column duplicate data
Replies
2
Views
203
jessiehayden
J
L
  • Question
show range based on a list of values in a column
Replies
1
Views
56
dermie_72
D
E
  • Question
Subtotal values and then hide rows
Replies
3
Views
114
Peter_SSs
Peter_SSs
N
  • Question
Stacked Chart to show value and status YTD
Replies
2
Views
294
neilo1969
N
S
  • Question
Cell values in same column summed to different cells based on color
Replies
3
Views
70
dermie_72
D

Forum statistics

Threads
1,214,952
Messages
6,122,458
Members
449,085
Latest member
ExcelError

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top