Awesome thank you it works great!!!!!!!!! My delicate typing ligaments thank
you!


"Gord Dibben" wrote:

=LOOKUP(B2,$P$1:$Q$30,$Q$1:$Q$30)

To see the reason for this go to help on absolute and relative referencing


Gord Dibben MS Excel MVP


On Mon, 1 Feb 2010 12:47:03 -0800, McRibIsBack
wrote:

I'm trying that, but when I copy the forumula it tries to updates the cells,
but I need the formula in a few hundred rows so i just need it to update the
lookup value and NOT the lookup vector. Is that possible?

what's happening now:

This is copied from A2: "=LOOKUP(B2, P1:Q30, Q1:Q30)"
To A3 as: "=LOOKUP(B3, P2:Q31, Q2:Q31)"

What I need to happen:

A3 = "=LOOKUP(B3, P1:Q30, Q1:Q30)"
A4 = "=LOOKUP(B4, P1:Q30, Q1:Q30)" etc....

How do I fill the cells and only have the lookup value updated? It will
take me forever to manually fix each cell one by one :(

"Don Guillett" wrote:

Why not a simple VLOOKUP function using a table?
1 NC


--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"McRibIsBack" wrote in message
...
Hi, I haven't used arrays in a while so forgive me. I'm converting
numeric
values that represent the U.S. states into the text values. If B1 = 1,
then
A1.value = NC, If B1 = 5, then A1.value = SC. I was making a case
structure
for this, but this will take me forever because I need to check every
state
AND I need to check the entire column "B"! I realized an array would be
better, but I can't remember how to build them correctly. Here is the
case
structure I was making:
Sub EntityCheck()

Select Case Range("B2").Value

Case 1
Range("A2").Value = "NC"

Case 5
Range("A2").Value = "SC"

Case 35
Range("A2").Value = "NJ"

Case 75
Range("A2").Value = "FL"

Case 99
Range("A2").Value = "TX"

Case 172
Range("A2").Value = "GA"

End Select


I appreciate any help you can offer!

.


.