Vlookup within multiple ranges

D

djinatlga

How do I do the following in Excel 2003?

I have a column of six digit numbers (About 24,000 Rows)
Example:

154563
345678
843565
....


I need to associate each one of those numbers to the following
parameters:
Column A, Column B, Column C
From, To, Equals

221100, 221199, "A"
443000, 443999, "B"
511110,511119, "C"
.....
If the number does not fall between the given sets it should show up as
"#N/A" or "Other"

I know how to do a vlookup by only using the mininum number of the set
and setting it to "TRUE" but I am finding it is assocaiting some
numbers to a set that it should not fall into.

Thanks!
 
M

Max

One way ..

Assuming numbers in A2 down,

Put in B2, and array-enter the formula,
ie press CTRL+SHIFT+ENTER (instead of just pressing ENTER):
=INDEX({"A";"B";"C"},MATCH(1,({221100;443000;511110}<=A2)*({221199;443999;511119}>=A2),0))
Copy B2 down as far as required
 
D

Dave Peterson

You could use this array formula:

=INDEX(Sheet1!C1:C99,MATCH(1,(Sheet1!A1:A99<=A1)*(Sheet1!B1:B99>=A1),0))

(I put my table in sheet1!A1:C99, adjust the range to match your data.)

This is an array formula. Hit ctrl-shift-enter instead of enter. If you do it
correctly, excel will wrap curly brackets {} around your formula. (don't type
them yourself.)

In xl2007, you should be able to use the whole column. In earlier versions, you
couldn't.
 
D

djinatlga

In your formula, after "MATCH( you put "1". Why "1"?
What is "A1" is that "154563" or "221100"?

Sorry that I am not following.
 
D

Dave Peterson

A1 is any number that you want (154563). Sheet1 will contain the lower limits
in column A and the upper limits in column B.

This portion:
(Sheet1!A1:A99<=A1)*(Sheet1!B1:B99>=A1)
returns a series of 1's and 0's.

If the value in A1 is trapped between what's in sheet1 (a1:b1), then a 1 is
returned. If it's outside either of those limits, then a 0 is returned.

Match(1,thatseriesof1'sand0's,0) will return the first 1 that's found--the first
row that traps that value in A1 between those two limits.
 
D

djinatlga

Thanks for the help! Got it working!

A1 is any number that you want (154563). Sheet1 will contain the lower limits
in column A and the upper limits in column B.

This portion:
(Sheet1!A1:A99<=A1)*(Sheet1!B1:B99>=A1)
returns a series of 1's and 0's.

If the value in A1 is trapped between what's in sheet1 (a1:b1), then a 1 is
returned. If it's outside either of those limits, then a 0 is returned.

Match(1,thatseriesof1'sand0's,0) will return the first 1 that's found--the first
row that traps that value in A1 between those two limits.








Dave Peterson- Hide quoted text -- Show quoted text -
Click to expand...
 
Top