VLOOKUP: search for a value within a range of cells

**sammyserwar** · 10-20-2009, 07:53 PM

I have a problem here. I want to do a vlookup an an array of cells but my array is complicated. I have cells in my array that is a range of values: 54820-55200 for example. This range is entered into 1 cell. I also have about 300 other cells with ranges similar to this like so:

Column A Column B
54820-55200 Yes
64573-64595Yes
93505-93562Yes
55300-55866Yes
64702-64726Yes
93580-93603Yes

I want to do a vlookup so that the value in the cell i want to lookup, let's say the value is 54821, then excel will return the value in the column next to the range (Yes in this case). If the value is 55201 then it should show a "#N/A"

How do i do this?

-Serwar

**Chance2** · 10-20-2009, 08:20 PM

You could try this formula...

Please Login or Register  to view this content.

**martindwilson** · 10-20-2009, 09:14 PM

or
=IF(AND(C1>=--LEFT(A1,5),C1<=--RIGHT(A1,5)),B1,"#N/A")
both of these depend on there being 5 digits either side of the -
you might want to consider
=IF(AND(C1>=--LEFT(A1,FIND("-",A1)-1),C1<=--MID(A1,FIND("-",A1)+1,255)),B1,"#N/A")

**DonkeyOte** · 10-21-2009, 03:12 AM

I think perhaps you mean something like:

Table: A1:B6
Criteria: C1
Result: D1

D1:
=LOOKUP(2,1/((0+LEFT(A1:A6,FIND("-",A1:A6)-1)<=C1)*(0+RIGHT(A1:A6,LEN(A1:A6)-FIND("-",A1:A6))>=C1)),B1:B6)

Above assumes your ranges do not overlap....
if they do the value from B is returned for the last range in which criteria is found (if first is required you can revert to an INDEX but the above is perhaps simplest)

**sammyserwar** · 10-21-2009, 11:45 AM

The last one seemed to work. Thanks! Here's a followup question: Some of my ranges have alphanumeric values:

G0431-G0530

The formula doesn't seem to be working on these ranges.

**DonkeyOte** · 10-21-2009, 02:49 PM

You don't specify what the "criteria" value would be for your last example (ie G0440 or 440) -- assuming the former then the below should work for you

Please Login or Register  to view this content.

(ie make comparisons text based)

VLOOKUP: search for a value within a range of cells

LinkBack

Thread Tools

Rate This Thread

Display

VLOOKUP: search for a value within a range of cells

Re: VLOOKUP: search for a value within a range of cells

Re: VLOOKUP: search for a value within a range of cells

Re: VLOOKUP: search for a value within a range of cells

Re: VLOOKUP: search for a value within a range of cells

Re: VLOOKUP: search for a value within a range of cells

Thread Information

Users Browsing this Thread

Bookmarks

Bookmarks

Posting Permissions