column to column

[reed0427]

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?

 

[Stephen]

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 here:
http://www.cpearson.com/excel/lookups.htm#ClosestMatch

 

[RAGdyer]

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(P2668-G12)),ABS(P2668-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

 

[reed0427]

Yikes! OK, here's the formula that isn't working:
(cell G15) =VLOOKUP(G13,1,P2668,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.

 

[reed0427]

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

 

[RagDyeR]

Appreciate the feed-back.