column to column
 

Here is what I am trying to do: In cell G15, I want it to look at cell G12
and compare G12 value to a list of values in P26 thru P68. Using closest
match, place the corresponding value from column Q. Example: If G12 is 5320,
look at column P and find closest match. That would be 5300 (P28). Place in
G15 the value in Q28.

I think I should be using VLOOKUP, correct?

column to column
 

"reed0427" wrote in message
...
Here is what I am trying to do: In cell G15, I want it to look at cell G12
and compare G12 value to a list of values in P26 thru P68. Using closest
match, place the corresponding value from column Q. Example: If G12 is
5320,
look at column P and find closest match. That would be 5300 (P28). Place
in
G15 the value in Q28.

I think I should be using VLOOKUP, correct?

For inexact matches, VLOOKUP will find the closest match that is smaller
than you are looking up. (See VLOOKUP in Help.) If you want the numerically
closest, look he
http://www.cpearson.com/excel/lookups.htm#ClosestMatch

column to column
 

Question:

By "closest", do you mean *either* over or under the lookup value?

For example - looking for 5320 -
Do you want 5321 to be selected over, say, 5300, even though 5321 is larger
then 5320?

If so, try this *array* formula:

=INDEX(Q26:Q68,MATCH(MIN(ABS(P26:P68-G12)),ABS(P26:P68-G12),0))

--
Array formulas are entered using CSE, <Ctrl <Shift <Enter, instead of the
regular <Enter, which will *automatically* enclose the formula in curly
brackets, which *cannot* be done manually.
--
On the other hand, if you want the closest match, *without going over* the
lookup value, you can use Lookup, or Vlookup,
*BUT*, the data in Column P *must* be sorted, ascending.

For example:

=LOOKUP(G12,P26:Q68)
OR
=VLOOKUP(G12,P26:Q68,2)

The last 2 examples are entered normally, with a simple <Enter.

HTH,

RD

----------------------------------------------------------------------------------------
Please keep all correspondence within the NewsGroup, so all may benefit !
----------------------------------------------------------------------------------------


"reed0427" wrote in message
...
Here is what I am trying to do: In cell G15, I want it to look at cell G12
and compare G12 value to a list of values in P26 thru P68. Using closest
match, place the corresponding value from column Q. Example: If G12 is
5320,
look at column P and find closest match. That would be 5300 (P28). Place
in
G15 the value in Q28.

I think I should be using VLOOKUP, correct?

column to column
 

Yikes! OK, here's the formula that isn't working:
(cell G15) =VLOOKUP(G13,1,P26:P68,2)

G13: This value is, for example, 5145.
P26 thru P68 is 3800-5400. Closest match is 5100 (P32)
Q26 thr Q68 is 78.0-74.0. Q32 is 77.0.

I want Q32's 77.0 to appear in G15). I can't seem to get the formula right.

"Stephen" wrote:

"reed0427" wrote in message
...
Here is what I am trying to do: In cell G15, I want it to look at cell G12
and compare G12 value to a list of values in P26 thru P68. Using closest
match, place the corresponding value from column Q. Example: If G12 is
5320,
look at column P and find closest match. That would be 5300 (P28). Place
in
G15 the value in Q28.

I think I should be using VLOOKUP, correct?

For inexact matches, VLOOKUP will find the closest match that is smaller
than you are looking up. (See VLOOKUP in Help.) If you want the numerically
closest, look he
http://www.cpearson.com/excel/lookups.htm#ClosestMatch

column to column
 

That's it, =VLOOKUP(G12,P26:Q68,2) did the trick. Thanks RAGdyer!

"RAGdyer" wrote:

Question:

By "closest", do you mean *either* over or under the lookup value?

For example - looking for 5320 -
Do you want 5321 to be selected over, say, 5300, even though 5321 is larger
then 5320?

If so, try this *array* formula:

=INDEX(Q26:Q68,MATCH(MIN(ABS(P26:P68-G12)),ABS(P26:P68-G12),0))

--
Array formulas are entered using CSE, <Ctrl <Shift <Enter, instead of the
regular <Enter, which will *automatically* enclose the formula in curly
brackets, which *cannot* be done manually.
--
On the other hand, if you want the closest match, *without going over* the
lookup value, you can use Lookup, or Vlookup,
*BUT*, the data in Column P *must* be sorted, ascending.

For example:

=LOOKUP(G12,P26:Q68)
OR
=VLOOKUP(G12,P26:Q68,2)

The last 2 examples are entered normally, with a simple <Enter.

HTH,

RD

----------------------------------------------------------------------------------------
Please keep all correspondence within the NewsGroup, so all may benefit !
----------------------------------------------------------------------------------------


"reed0427" wrote in message
...
Here is what I am trying to do: In cell G15, I want it to look at cell G12
and compare G12 value to a list of values in P26 thru P68. Using closest
match, place the corresponding value from column Q. Example: If G12 is
5320,
look at column P and find closest match. That would be 5300 (P28). Place
in
G15 the value in Q28.

I think I should be using VLOOKUP, correct?

column to column
 

Appreciate the feed-back.
--

Regards,

RD
-----------------------------------------------------------------------------------------------
Please keep all correspondence within the Group, so all may benefit !
-----------------------------------------------------------------------------------------------

"reed0427" wrote in message
...
That's it, =VLOOKUP(G12,P26:Q68,2) did the trick. Thanks RAGdyer!

"RAGdyer" wrote:

Question:

By "closest", do you mean *either* over or under the lookup value?

For example - looking for 5320 -
Do you want 5321 to be selected over, say, 5300, even though 5321 is
larger
then 5320?

If so, try this *array* formula:

=INDEX(Q26:Q68,MATCH(MIN(ABS(P26:P68-G12)),ABS(P26:P68-G12),0))

--
Array formulas are entered using CSE, <Ctrl <Shift <Enter, instead of
the
regular <Enter, which will *automatically* enclose the formula in curly
brackets, which *cannot* be done manually.
--
On the other hand, if you want the closest match, *without going over* the
lookup value, you can use Lookup, or Vlookup,
*BUT*, the data in Column P *must* be sorted, ascending.

For example:

=LOOKUP(G12,P26:Q68)
OR
=VLOOKUP(G12,P26:Q68,2)

The last 2 examples are entered normally, with a simple <Enter.

HTH,

RD

----------------------------------------------------------------------------------------
Please keep all correspondence within the NewsGroup, so all may benefit !
----------------------------------------------------------------------------------------


"reed0427" wrote in message
...
Here is what I am trying to do: In cell G15, I want it to look at cell
G12
and compare G12 value to a list of values in P26 thru P68. Using closest
match, place the corresponding value from column Q. Example: If G12 is
5320,
look at column P and find closest match. That would be 5300 (P28). Place
in
G15 the value in Q28.

I think I should be using VLOOKUP, correct?