Reverse lookup with if's

A

adowdy

New Member
Joined
May 31, 2011
Messages
3
Hello all,

I am no genius with lookup functions, so I've been trying this with no success. The table below is an abbreviated snippet of a larger sheet.

<TABLE style="WIDTH: 444pt; BORDER-COLLAPSE: collapse" cellSpacing=0 cellPadding=0 width=592 border=0><COLGROUP><COL style="WIDTH: 56pt; mso-width-source: userset; mso-width-alt: 2104" width=74><COL style="WIDTH: 52pt; mso-width-source: userset; mso-width-alt: 1962" width=69><COL style="WIDTH: 63pt; mso-width-source: userset; mso-width-alt: 2389" width=84><COL style="WIDTH: 65pt; mso-width-source: userset; mso-width-alt: 2474" width=87><COL style="WIDTH: 68pt; mso-width-source: userset; mso-width-alt: 2588" span=2 width=91><COL style="WIDTH: 72pt; mso-width-source: userset; mso-width-alt: 2730" width=96><TBODY><TR style="HEIGHT: 26.25pt; mso-height-source: userset" height=35><TD class=xl76 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 1pt solid; BORDER-LEFT: windowtext 1pt solid; WIDTH: 56pt; BORDER-BOTTOM: windowtext 1pt solid; HEIGHT: 26.25pt; BACKGROUND-COLOR: transparent" width=74 height=35></TD><TD class=xl77 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 1pt solid; BORDER-LEFT: windowtext; WIDTH: 52pt; BORDER-BOTTOM: windowtext 1pt solid; BACKGROUND-COLOR: transparent" width=69></TD><TD class=xl78 style="BORDER-RIGHT: windowtext 1pt solid; BORDER-TOP: windowtext 1pt solid; BORDER-LEFT: windowtext; WIDTH: 63pt; BORDER-BOTTOM: windowtext 1pt solid; BACKGROUND-COLOR: transparent" width=84></TD><TD class=xl70 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 0.5pt solid; BORDER-LEFT: windowtext 1pt solid; WIDTH: 65pt; BORDER-BOTTOM: windowtext 1pt solid; BACKGROUND-COLOR: transparent" width=87>75</TD><TD class=xl75 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 0.5pt solid; BORDER-LEFT: #ece9d8; WIDTH: 68pt; BORDER-BOTTOM: windowtext 1pt solid; BACKGROUND-COLOR: transparent" width=91>70</TD><TD class=xl71 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 0.5pt solid; BORDER-LEFT: windowtext; WIDTH: 68pt; BORDER-BOTTOM: windowtext 1pt solid; BACKGROUND-COLOR: transparent" width=91>65</TD><TD class=xl71 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 0.5pt solid; BORDER-LEFT: windowtext; WIDTH: 72pt; BORDER-BOTTOM: windowtext 1pt solid; BACKGROUND-COLOR: transparent" width=96>60</TD></TR><TR style="HEIGHT: 12.75pt; mso-height-source: userset" height=17><TD class=xl72 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 1pt solid; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" height=17>3.721</TD><TD class=xl68 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent">8.779</TD><TD class=xl73 dir=ltr style="BORDER-RIGHT: windowtext 1pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent">-7.723</TD><TD class=xl69 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 0.5pt solid; FONT-WEIGHT: 400; FONT-SIZE: 10pt; BACKGROUND: #ff3300; BORDER-LEFT: windowtext 0.5pt solid; COLOR: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; FONT-FAMILY: Arial; TEXT-DECORATION: none; text-underline-style: none; text-line-through: none; mso-pattern: black none">579</TD><TD class=xl69 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 0.5pt solid; FONT-WEIGHT: 400; FONT-SIZE: 10pt; BACKGROUND: #ff3300; BORDER-LEFT: windowtext 0.5pt solid; COLOR: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; FONT-FAMILY: Arial; TEXT-DECORATION: none; text-underline-style: none; text-line-through: none; mso-pattern: black none">505</TD><TD class=xl69 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 0.5pt solid; FONT-WEIGHT: 400; FONT-SIZE: 10pt; BACKGROUND: #ff3300; BORDER-LEFT: windowtext 0.5pt solid; COLOR: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; FONT-FAMILY: Arial; TEXT-DECORATION: none; text-underline-style: none; text-line-through: none; mso-pattern: black none">435</TD><TD class=xl69 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 0.5pt solid; FONT-WEIGHT: 400; FONT-SIZE: 10pt; BACKGROUND: #ffff66; BORDER-LEFT: windowtext 0.5pt solid; COLOR: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; FONT-FAMILY: Arial; TEXT-DECORATION: none; text-underline-style: none; text-line-through: none; mso-pattern: #FFCC00 none">371</TD></TR><TR style="HEIGHT: 12.75pt; mso-height-source: userset" height=17><TD class=xl72 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 1pt solid; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" height=17>3.473</TD><TD class=xl68 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent">9.027</TD><TD class=xl73 dir=ltr style="BORDER-RIGHT: windowtext 1pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent">-7.156</TD><TD class=xl69 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 0.5pt solid; FONT-WEIGHT: 400; FONT-SIZE: 10pt; BACKGROUND: #ff3300; BORDER-LEFT: windowtext 0.5pt solid; COLOR: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; FONT-FAMILY: Arial; TEXT-DECORATION: none; text-underline-style: none; text-line-through: none; mso-pattern: black none">497</TD><TD class=xl69 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 0.5pt solid; FONT-WEIGHT: 400; FONT-SIZE: 10pt; BACKGROUND: #ff3300; BORDER-LEFT: windowtext 0.5pt solid; COLOR: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; FONT-FAMILY: Arial; TEXT-DECORATION: none; text-underline-style: none; text-line-through: none; mso-pattern: black none">433</TD><TD class=xl69 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 0.5pt solid; FONT-WEIGHT: 400; FONT-SIZE: 10pt; BACKGROUND: #ffff66; BORDER-LEFT: windowtext 0.5pt solid; COLOR: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; FONT-FAMILY: Arial; TEXT-DECORATION: none; text-underline-style: none; text-line-through: none; mso-pattern: #FFCC00 none">373</TD><TD class=xl69 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 0.5pt solid; FONT-WEIGHT: 400; FONT-SIZE: 10pt; BACKGROUND: #ffff66; BORDER-LEFT: windowtext 0.5pt solid; COLOR: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; FONT-FAMILY: Arial; TEXT-DECORATION: none; text-underline-style: none; text-line-through: none; mso-pattern: #FFCC00 none">318</TD></TR><TR style="HEIGHT: 12.75pt; mso-height-source: userset" height=17><TD class=xl72 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 1pt solid; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" height=17>3.225</TD><TD class=xl68 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent">9.275</TD><TD class=xl73 dir=ltr style="BORDER-RIGHT: windowtext 1pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent">-6.588</TD><TD class=xl69 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 0.5pt solid; FONT-WEIGHT: 400; FONT-SIZE: 10pt; BACKGROUND: #ff3300; BORDER-LEFT: windowtext 0.5pt solid; COLOR: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; FONT-FAMILY: Arial; TEXT-DECORATION: none; text-underline-style: none; text-line-through: none; mso-pattern: black none">421</TD><TD class=xl69 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 0.5pt solid; FONT-WEIGHT: 400; FONT-SIZE: 10pt; BACKGROUND: #ffff66; BORDER-LEFT: windowtext 0.5pt solid; COLOR: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; FONT-FAMILY: Arial; TEXT-DECORATION: none; text-underline-style: none; text-line-through: none; mso-pattern: #FFCC00 none">367</TD><TD class=xl69 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 0.5pt solid; FONT-WEIGHT: 400; FONT-SIZE: 10pt; BACKGROUND: #ffff66; BORDER-LEFT: windowtext 0.5pt solid; COLOR: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; FONT-FAMILY: Arial; TEXT-DECORATION: none; text-underline-style: none; text-line-through: none; mso-pattern: #FFCC00 none">316</TD><TD class=xl69 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 0.5pt solid; FONT-WEIGHT: 400; FONT-SIZE: 10pt; BACKGROUND: #ffff66; BORDER-LEFT: windowtext 0.5pt solid; COLOR: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; FONT-FAMILY: Arial; TEXT-DECORATION: none; text-underline-style: none; text-line-through: none; mso-pattern: #FFCC00 none">269</TD></TR><TR style="HEIGHT: 12.75pt; mso-height-source: userset" height=17><TD class=xl72 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 1pt solid; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" height=17>2.977</TD><TD class=xl68 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent">9.523</TD><TD class=xl73 dir=ltr style="BORDER-RIGHT: windowtext 1pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent">-6.019</TD><TD class=xl69 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 0.5pt solid; FONT-WEIGHT: 400; FONT-SIZE: 10pt; BACKGROUND: #ffff66; BORDER-LEFT: windowtext 0.5pt solid; COLOR: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; FONT-FAMILY: Arial; TEXT-DECORATION: none; text-underline-style: none; text-line-through: none; mso-pattern: #FFCC00 none">351</TD><TD class=xl69 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 0.5pt solid; FONT-WEIGHT: 400; FONT-SIZE: 10pt; BACKGROUND: #ffff66; BORDER-LEFT: windowtext 0.5pt solid; COLOR: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; FONT-FAMILY: Arial; TEXT-DECORATION: none; text-underline-style: none; text-line-through: none; mso-pattern: #FFCC00 none">306</TD><TD class=xl69 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 0.5pt solid; FONT-WEIGHT: 400; FONT-SIZE: 10pt; BACKGROUND: #ffff66; BORDER-LEFT: windowtext 0.5pt solid; COLOR: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; FONT-FAMILY: Arial; TEXT-DECORATION: none; text-underline-style: none; text-line-through: none; mso-pattern: #FFCC00 none">264</TD><TD class=xl69 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 0.5pt solid; FONT-WEIGHT: 400; FONT-SIZE: 10pt; BACKGROUND: #339933; BORDER-LEFT: windowtext 0.5pt solid; COLOR: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; FONT-FAMILY: Arial; TEXT-DECORATION: none; text-underline-style: none; text-line-through: none; mso-pattern: black none">225</TD></TR><TR style="HEIGHT: 12.75pt; mso-height-source: userset" height=17><TD class=xl72 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 1pt solid; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" height=17>2.728</TD><TD class=xl68 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent">9.772</TD><TD class=xl73 dir=ltr style="BORDER-RIGHT: windowtext 1pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent">-5.451</TD><TD class=xl69 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 0.5pt solid; FONT-WEIGHT: 400; FONT-SIZE: 10pt; BACKGROUND: #ffff66; BORDER-LEFT: windowtext 0.5pt solid; COLOR: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; FONT-FAMILY: Arial; TEXT-DECORATION: none; text-underline-style: none; text-line-through: none; mso-pattern: #FFCC00 none">288</TD><TD class=xl69 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 0.5pt solid; FONT-WEIGHT: 400; FONT-SIZE: 10pt; BACKGROUND: #ffff66; BORDER-LEFT: windowtext 0.5pt solid; COLOR: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; FONT-FAMILY: Arial; TEXT-DECORATION: none; text-underline-style: none; text-line-through: none; mso-pattern: #FFCC00 none">251</TD><TD class=xl69 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 0.5pt solid; FONT-WEIGHT: 400; FONT-SIZE: 10pt; BACKGROUND: #339933; BORDER-LEFT: windowtext 0.5pt solid; COLOR: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; FONT-FAMILY: Arial; TEXT-DECORATION: none; text-underline-style: none; text-line-through: none; mso-pattern: black none">216</TD><TD class=xl69 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 0.5pt solid; FONT-WEIGHT: 400; FONT-SIZE: 10pt; BACKGROUND: #339933; BORDER-LEFT: windowtext 0.5pt solid; COLOR: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; FONT-FAMILY: Arial; TEXT-DECORATION: none; text-underline-style: none; text-line-through: none; mso-pattern: black none">184</TD></TR></TBODY></TABLE>

If I use a value from the second column as an input, say 9.523, how can I return the number in the top row (in bold) that corresponds to a data value in the colored range which is above a certain threshold? In this case the threshold is 250.

So for this example, if 9.523 is my input then I would go across that row and find the max value under 250 (which is 225 in this case), and then say "ok, 60 is my answer". I need a way to spit out that '60' into a cell as an output automatically.

I'd greatly appreciate any input. Thanks.
 

Excel Facts

Move date out one month or year
Use =EDATE(A2,1) for one month later. Use EDATE(A2,12) for one year later.
Sort by date Sort by votes
Given your sample of data, the below should work. Post back if it doesn't work with a larger dataset:

Excel Workbook
ABCDEFGHIJK
175706560Value 1:9.523
23.7218.779-7.723579505435371value 2:250
33.4739.027-7.156497433373318Answer:60
43.2259.275-6.588421367316269
52.9779.523-6.019351306264225
62.7289.772-5.451288251216184
Sheet1
 
Upvote 0
jreedich said:
Just a question. Where does the 250 come from?
Click to expand...

The '250' is a maximum recommended acceleration; the values in the colored range are calculated by a formula which uses many parameters not listed/shown here.

njimack said:
Given your sample of data, the below should work. Post back if it doesn't work with a larger dataset:
Click to expand...

I just tried that and it seems to return the smallest value in the top row. Using the same '9.523' input row, I get a value of 25 instead of the 60 it should be.

<TABLE style="WIDTH: 817pt; BORDER-COLLAPSE: collapse" cellSpacing=0 cellPadding=0 width=1086 border=0><COLGROUP><COL style="WIDTH: 56pt; mso-width-source: userset; mso-width-alt: 2104" width=74><COL style="WIDTH: 52pt; mso-width-source: userset; mso-width-alt: 1962" width=69><COL style="WIDTH: 63pt; mso-width-source: userset; mso-width-alt: 2389" width=84><COL style="WIDTH: 62pt; mso-width-source: userset; mso-width-alt: 2332" width=82><COL style="WIDTH: 65pt; mso-width-source: userset; mso-width-alt: 2474" width=87><COL style="WIDTH: 68pt; mso-width-source: userset; mso-width-alt: 2588" span=2 width=91><COL style="WIDTH: 72pt; mso-width-source: userset; mso-width-alt: 2730" width=96><COL style="WIDTH: 43pt; mso-width-source: userset; mso-width-alt: 1621" span=2 width=57><COL style="WIDTH: 42pt; mso-width-source: userset; mso-width-alt: 1592" span=2 width=56><COL style="WIDTH: 47pt" span=3 width=62><TBODY><TR style="HEIGHT: 26.25pt; mso-height-source: userset" height=35><TD class=xl73 id=td_post_2735468 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 1pt solid; BORDER-LEFT: windowtext 1pt solid; WIDTH: 56pt; BORDER-BOTTOM: windowtext 1pt solid; HEIGHT: 26.25pt; BACKGROUND-COLOR: transparent" width=74 height=35></TD><TD class=xl74 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 1pt solid; BORDER-LEFT: windowtext; WIDTH: 52pt; BORDER-BOTTOM: windowtext 1pt solid; BACKGROUND-COLOR: transparent" width=69></TD><TD class=xl75 style="BORDER-RIGHT: windowtext 1pt solid; BORDER-TOP: windowtext 1pt solid; BORDER-LEFT: windowtext; WIDTH: 63pt; BORDER-BOTTOM: windowtext 1pt solid; BACKGROUND-COLOR: transparent" width=84></TD><TD class=xl76 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: windowtext 0.5pt solid; BORDER-LEFT: #ece9d8; WIDTH: 62pt; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent" width=82></TD><TD class=xl68 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 0.5pt solid; BORDER-LEFT: windowtext 1pt solid; WIDTH: 65pt; BORDER-BOTTOM: windowtext 1pt solid; BACKGROUND-COLOR: transparent" width=87>75</TD><TD class=xl71 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 0.5pt solid; BORDER-LEFT: #ece9d8; WIDTH: 68pt; BORDER-BOTTOM: windowtext 1pt solid; BACKGROUND-COLOR: transparent" width=91>70</TD><TD class=xl69 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 0.5pt solid; BORDER-LEFT: windowtext; WIDTH: 68pt; BORDER-BOTTOM: windowtext 1pt solid; BACKGROUND-COLOR: transparent" width=91>65</TD><TD class=xl69 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 0.5pt solid; BORDER-LEFT: windowtext; WIDTH: 72pt; BORDER-BOTTOM: windowtext 1pt solid; BACKGROUND-COLOR: transparent" width=96>60</TD><TD class=xl69 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 0.5pt solid; BORDER-LEFT: windowtext; WIDTH: 43pt; BORDER-BOTTOM: windowtext 1pt solid; BACKGROUND-COLOR: transparent" width=57>55</TD><TD class=xl69 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 0.5pt solid; BORDER-LEFT: windowtext; WIDTH: 43pt; BORDER-BOTTOM: windowtext 1pt solid; BACKGROUND-COLOR: transparent" width=57>50</TD><TD class=xl69 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 0.5pt solid; BORDER-LEFT: windowtext; WIDTH: 42pt; BORDER-BOTTOM: windowtext 1pt solid; BACKGROUND-COLOR: transparent" width=56>45</TD><TD class=xl69 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 0.5pt solid; BORDER-LEFT: windowtext; WIDTH: 42pt; BORDER-BOTTOM: windowtext 1pt solid; BACKGROUND-COLOR: transparent" width=56>40</TD><TD class=xl70 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 0.5pt solid; BORDER-LEFT: windowtext; WIDTH: 47pt; BORDER-BOTTOM: windowtext 1pt solid; BACKGROUND-COLOR: transparent" width=62>35</TD><TD class=xl70 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 0.5pt solid; BORDER-LEFT: windowtext; WIDTH: 47pt; BORDER-BOTTOM: windowtext 1pt solid; BACKGROUND-COLOR: transparent" width=62>30</TD><TD class=xl72 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: windowtext 0.5pt solid; BORDER-LEFT: windowtext; WIDTH: 47pt; BORDER-BOTTOM: windowtext 1pt solid; BACKGROUND-COLOR: transparent" width=62>25</TD></TR></TBODY></TABLE>
<TABLE style="WIDTH: 833pt; BORDER-COLLAPSE: collapse" cellSpacing=0 cellPadding=0 width=1107 border=0><COLGROUP><COL style="WIDTH: 56pt; mso-width-source: userset; mso-width-alt: 2104" width=74><COL style="WIDTH: 52pt; mso-width-source: userset; mso-width-alt: 1962" width=69><COL style="WIDTH: 63pt; mso-width-source: userset; mso-width-alt: 2389" width=84><COL style="WIDTH: 62pt; mso-width-source: userset; mso-width-alt: 2332" width=82><COL style="WIDTH: 65pt; mso-width-source: userset; mso-width-alt: 2474" width=87><COL style="WIDTH: 68pt; mso-width-source: userset; mso-width-alt: 2588" span=2 width=91><COL style="WIDTH: 72pt; mso-width-source: userset; mso-width-alt: 2730" width=96><COL style="WIDTH: 59pt; mso-width-source: userset; mso-width-alt: 2218" width=78><COL style="WIDTH: 43pt; mso-width-source: userset; mso-width-alt: 1621" width=57><COL style="WIDTH: 42pt; mso-width-source: userset; mso-width-alt: 1592" span=2 width=56><COL style="WIDTH: 47pt" span=3 width=62><TBODY><TR style="HEIGHT: 12.75pt; mso-height-source: userset" height=17><TD class=xl70 id=td_post_2735468 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 0.5pt solid; BORDER-LEFT: windowtext 1pt solid; WIDTH: 56pt; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" width=74 height=17>2.977</TD><TD class=xl68 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 0.5pt solid; BORDER-LEFT: windowtext; WIDTH: 52pt; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent" width=69>9.523</TD><TD class=xl71 dir=ltr style="BORDER-RIGHT: windowtext 1pt solid; BORDER-TOP: windowtext 0.5pt solid; BORDER-LEFT: windowtext; WIDTH: 63pt; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent" width=84>-6.019</TD><TD class=xl72 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: windowtext 0.5pt solid; BORDER-LEFT: #ece9d8; WIDTH: 62pt; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent" width=82>-0.105</TD><TD class=xl69 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 0.5pt solid; FONT-WEIGHT: 400; FONT-SIZE: 10pt; BACKGROUND: #ffff66; BORDER-LEFT: windowtext 0.5pt solid; WIDTH: 65pt; COLOR: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; FONT-FAMILY: Arial; TEXT-DECORATION: none; text-underline-style: none; text-line-through: none; mso-pattern: #FFCC00 none" width=87>351</TD><TD class=xl69 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 0.5pt solid; FONT-WEIGHT: 400; FONT-SIZE: 10pt; BACKGROUND: #ffff66; BORDER-LEFT: windowtext 0.5pt solid; WIDTH: 68pt; COLOR: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; FONT-FAMILY: Arial; TEXT-DECORATION: none; text-underline-style: none; text-line-through: none; mso-pattern: #FFCC00 none" width=91>306</TD><TD class=xl69 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 0.5pt solid; FONT-WEIGHT: 400; FONT-SIZE: 10pt; BACKGROUND: #ffff66; BORDER-LEFT: windowtext 0.5pt solid; WIDTH: 68pt; COLOR: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; FONT-FAMILY: Arial; TEXT-DECORATION: none; text-underline-style: none; text-line-through: none; mso-pattern: #FFCC00 none" width=91>264</TD><TD class=xl69 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 0.5pt solid; FONT-WEIGHT: 400; FONT-SIZE: 10pt; BACKGROUND: #339933; BORDER-LEFT: windowtext 0.5pt solid; WIDTH: 72pt; COLOR: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; FONT-FAMILY: Arial; TEXT-DECORATION: none; text-underline-style: none; text-line-through: none; mso-pattern: black none" width=96>225</TD><TD class=xl69 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 0.5pt solid; FONT-WEIGHT: 400; FONT-SIZE: 10pt; BACKGROUND: #339933; BORDER-LEFT: windowtext 0.5pt solid; WIDTH: 59pt; COLOR: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; FONT-FAMILY: Arial; TEXT-DECORATION: none; text-underline-style: none; text-line-through: none; mso-pattern: black none" width=78>189</TD><TD class=xl69 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 0.5pt solid; FONT-WEIGHT: 400; FONT-SIZE: 10pt; BACKGROUND: #339933; BORDER-LEFT: windowtext 0.5pt solid; WIDTH: 43pt; COLOR: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; FONT-FAMILY: Arial; TEXT-DECORATION: none; text-underline-style: none; text-line-through: none; mso-pattern: black none" width=57>156</TD><TD class=xl69 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 0.5pt solid; FONT-WEIGHT: 400; FONT-SIZE: 10pt; BACKGROUND: #339933; BORDER-LEFT: windowtext 0.5pt solid; WIDTH: 42pt; COLOR: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; FONT-FAMILY: Arial; TEXT-DECORATION: none; text-underline-style: none; text-line-through: none; mso-pattern: black none" width=56>127</TD><TD class=xl69 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 0.5pt solid; FONT-WEIGHT: 400; FONT-SIZE: 10pt; BACKGROUND: #339933; BORDER-LEFT: windowtext 0.5pt solid; WIDTH: 42pt; COLOR: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; FONT-FAMILY: Arial; TEXT-DECORATION: none; text-underline-style: none; text-line-through: none; mso-pattern: black none" width=56>100</TD><TD class=xl69 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 0.5pt solid; FONT-WEIGHT: 400; FONT-SIZE: 10pt; BACKGROUND: #339933; BORDER-LEFT: windowtext 0.5pt solid; WIDTH: 47pt; COLOR: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; FONT-FAMILY: Arial; TEXT-DECORATION: none; text-underline-style: none; text-line-through: none; mso-pattern: black none" width=62>77</TD><TD class=xl69 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 0.5pt solid; FONT-WEIGHT: 400; FONT-SIZE: 10pt; BACKGROUND: #339933; BORDER-LEFT: windowtext 0.5pt solid; WIDTH: 47pt; COLOR: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; FONT-FAMILY: Arial; TEXT-DECORATION: none; text-underline-style: none; text-line-through: none; mso-pattern: black none" width=62>56</TD><TD class=xl69 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 0.5pt solid; FONT-WEIGHT: 400; FONT-SIZE: 10pt; BACKGROUND: #339933; BORDER-LEFT: windowtext 0.5pt solid; WIDTH: 47pt; COLOR: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; FONT-FAMILY: Arial; TEXT-DECORATION: none; text-underline-style: none; text-line-through: none; mso-pattern: black none" width=62>39</TD></TR></TBODY></TABLE>
 
Upvote 0
Never mind - I realized I could just change the 'min' function to 'max' and it works just like it should now. Awesome, thanks.
 
Upvote 0
adowdy said:
Never mind - I realized I could just change the 'min' function to 'max' and it works just like it should now. Awesome, thanks.
Click to expand...

Glad it works - in the meantime I've written a UDF that might be a better fit for you...

Excel Workbook
ABCDEFGHIJKLMNOPQ
17570656055504540353025Value 1:9.523
23.7218.779-7.723579505435371value 2:250
33.4739.027-7.156497433373318Answer:65
43.2259.275-6.588421367316269
52.9779.523-6.019351306264225189156127100775639
62.7289.772-5.451288251216184
Sheet1


Code: 
Function RLookup(V1 As Range, V2 As Range, col As Range)
Dim x As Long
Dim s As Long

x = WorksheetFunction.Match(V1, col, 0) + 1
s = WorksheetFunction.Match(V2, Range(Cells(x, 4), Cells(x, 14)), -1) + 3

RLookup = Cells(1, s)

End Function
 
Upvote 0
You must log in or register to reply here.

Similar threads

D
  • Solved
VBA Function to Paste Table into Outlook Email Resulting in HTML language in Email
Replies
8
Views
624
smozgur
smozgur
A
  • Question Question
VBA - Automatically Break the Line in HTML format.
Replies
7
Views
2K
Attrazion
A
E
  • Question Question
VBA code clean up
Replies
9
Views
583
mumps
mumps
G
  • Question Question
Using Excel formula to create HTML code text referencing specific cells
Replies
0
Views
1K
gvsumich
G
Q
  • Solved
VBA: Dynamically Create Active X Frame and Yes / No Radio Button
2
Replies
13
Views
266
Domenic
D

Forum statistics

Threads
1,224,613
Messages
6,179,904
Members
452,948
Latest member
Dupuhini

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