Vlookup within multiple ranges

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

djinatlga - 20 Dec 2006 13:46 GMT

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!

Reply to this Message

Max - 20 Dec 2006 14:33 GMT

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

Signature

Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---

> How do I do the following in Excel 2003?
>
[quoted text clipped - 23 lines]
>
> Thanks!

Reply to this Message

Dave Peterson - 20 Dec 2006 14:36 GMT

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.

> How do I do the following in Excel 2003?
>
[quoted text clipped - 23 lines]
>
> Thanks!

Signature

Dave Peterson

Reply to this Message

djinatlga - 20 Dec 2006 15:15 GMT

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

Sorry that I am not following.

> You could use this array formula:
>
[quoted text clipped - 36 lines]
> >
> > Thanks!

Reply to this Message

Dave Peterson - 20 Dec 2006 15:27 GMT

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.

> In your formula, after "MATCH( you put "1". Why "1"?
> What is "A1" is that "154563" or "221100"?
[quoted text clipped - 45 lines]
> >
> > Dave Peterson

Signature

Dave Peterson

Reply to this Message

djinatlga - 20 Dec 2006 16:13 GMT

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.
[quoted text clipped - 60 lines]
>
> Dave Peterson- Hide quoted text -- Show quoted text -

Reply to this Message

Rate this thread: