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

[plwhittington]

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.

 

[Fluff]

Something like
=IFERROR(VLOOKUP(F5,J1:L30,3,0),IFERROR(VLOOKUP(F5,K1:L30,1,0),"Not found"))

 

[plwhittington]

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.

 

[plwhittington]

meant to say "true"

 

[Fluff]

In that case can you please post some sample data, there are some add-ins available here that let you do that. https://www.mrexcel.com/forum/about-board/508133-attachments.html

 

[plwhittington]

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]

Ok, how about

Excel 2013/2016
	F	G	H	I	J	K	L
2	Col F	G			J	K	L
3	75	Henry			1	10	Adam
4	61	Graham			15	22	Bill
5	11	Not found			29	40	Catherine
6	27	Not found			44	50	Diane
7					51	55	Ed
8					61	65	Graham
9					68	77	Henry
List

Cell Formulas
Range	Formula
G3	{=IFERROR(INDEX($L$3:$L$9,MATCH(1,($J$3:$J$9<=F3)*($K$3:$K$9>=F3),0)),"Not found")}
Press CTRL+SHIFT+ENTER to enter array formulas.

 

[plwhittington]

This is working- thanks so much- never used an array like this before.
Thanks again!

 

[Fluff]

You're welcome & thanks for the feedback