Looking up a value to see if it is valid in multiple ranges

plwhittington

Board Regular
Joined
May 10, 2007
Messages
131
I have a list of numeric values in Column F to look up. I am trying to see if the value in each row of column F is valid in ranges in columns J-K and if so, return the value in column L for that row into column G. If the value is not found in any of the ranges in columns J-K, return an error or "Not Found" in Column G.

I have tried VLOOKUPs and COUNTIFs, but cannot seem to get what I need.

Can anyone tell me what formula I could use in Column G?
Thx.
 

Some videos you may like

Excel Facts

Excel Can Read to You
Customize Quick Access Toolbar. From All Commands, add Speak Cells or Speak Cells on Enter to QAT. Select cells. Press Speak Cells.

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
46,310
Office Version
  1. 365
Platform
  1. Windows
Something like
=IFERROR(VLOOKUP(F5,J1:L30,3,0),IFERROR(VLOOKUP(F5,K1:L30,1,0),"Not found"))
 

plwhittington

Board Regular
Joined
May 10, 2007
Messages
131
Thanks, but did not work. For example, one value I'm looking up in Col F is 61. The range in Col J-K is 51-71 (Col J=51, Col K=71) so the formula is not finding it.

Also, I need it to be able to identify values that are not in any of the ranges, so if I add "false" to the VLOOKUP, it will assign a value even if it is not in a valid range.
 
Last edited:

plwhittington

Board Regular
Joined
May 10, 2007
Messages
131
Tried, but my network security won't let me navigate to these pages to download a tool that let me post a screenshot. Below is sample data if that helps.

Col F, G, J, K, L
75, Henry, 1, 10, Adam
61, Graham, 15, 22, Bill
11, Not Found, 29, 40, Catherine
27, Not Found, 44, 50, Diane
51, 55, Ed
61, 65, Graham
68, 77, Henry

The only formula I have in in Column G, which doesn't work, but the values I would expect to see are shown above.
Thanks again for any help you can give.
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
46,310
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

Ok, how about

<b>Excel 2013/2016</b><table cellpadding="2.5px" rules="all" style=";background-color: rgb(255,255,255);border: 1px solid;border-collapse: collapse; border-color: rgb(187,187,187)"><colgroup><col width="25px" style="background-color: rgb(218,231,245)" /><col /><col /><col /><col /><col /><col /><col /></colgroup><thead><tr style=" background-color: rgb(218,231,245);text-align: center;color: rgb(22,17,32)"><th></th><th>F</th><th>G</th><th>H</th><th>I</th><th>J</th><th>K</th><th>L</th></tr></thead><tbody><tr ><td style="color: rgb(22,17,32);text-align: center;">2</td><td style=";">Col F</td><td style=";"> G</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style=";"> J</td><td style=";"> K</td><td style=";"> L</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">3</td><td style="text-align: right;;">75</td><td style=";">Henry</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;">1</td><td style="text-align: right;;">10</td><td style=";">Adam</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">4</td><td style="text-align: right;;">61</td><td style=";">Graham</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;">15</td><td style="text-align: right;;">22</td><td style=";">Bill</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">5</td><td style="text-align: right;;">11</td><td style=";">Not found</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;">29</td><td style="text-align: right;;">40</td><td style=";">Catherine</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">6</td><td style="text-align: right;;">27</td><td style=";">Not found</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;">44</td><td style="text-align: right;;">50</td><td style=";">Diane</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">7</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;">51</td><td style="text-align: right;;">55</td><td style=";">Ed</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">8</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;">61</td><td style="text-align: right;;">65</td><td style=";">Graham</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">9</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;">68</td><td style="text-align: right;;">77</td><td style=";">Henry</td></tr></tbody></table><p style="width:3.2em;font-weight:bold;margin:0;padding:0.2em 0.6em 0.2em 0.5em;border: 1px solid rgb(187,187,187);border-top:none;text-align: center;background-color: rgb(218,231,245);color: rgb(22,17,32)">List</p><br /><br /><table width="85%" cellpadding="2.5px" rules="all" style=";border: 2px solid black;border-collapse:collapse;padding: 0.4em;background-color: rgb(255,255,255)" ><tr><td style="padding:6px" ><b>Array Formulas</b><table cellpadding="2.5px" width="100%" rules="all" style="border: 1px solid;text-align:center;background-color: rgb(255,255,255);border-collapse: collapse; border-color: rgb(187,187,187)"><thead><tr style=" background-color: rgb(218,231,245);color: rgb(22,17,32)"><th width="10px">Cell</th><th style="text-align:left;padding-left:5px;">Formula</th></tr></thead><tbody><tr><th width="10px" style=" background-color: rgb(218,231,245);color: rgb(22,17,32)">G3</th><td style="text-align:left">{=IFERROR(<font color="Blue">INDEX(<font color="Red">$L$3:$L$9,MATCH(<font color="Green">1,(<font color="Purple">$J$3:$J$9<=F3</font>)*(<font color="Purple">$K$3:$K$9>=F3</font>),0</font>)</font>),"Not found"</font>)}</td></tr></tbody></table><b>Entered with Ctrl+Shift+Enter.</b> If entered correctly, Excel will surround with curly braces {}.
<b>Note: Do not try and enter the {} manually yourself</b></td></tr></table><br />
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
46,310
Office Version
  1. 365
Platform
  1. Windows
You're welcome & thanks for the feedback
 

Watch MrExcel Video

Forum statistics

Threads
1,108,991
Messages
5,526,099
Members
409,685
Latest member
Bellybb

This Week's Hot Topics

Top