vlookup difficulty

[laneman]

Hi,

I'm trying to get vlookup to return two answers on an existin
spreadsheet of 470 rows and 9 columns. I want to set it up so that whe
entering a number in any cell down column A, Excel will fill in th
correct answer in column B and in column E. I can only get one row t
work at a time. I noticed when I paste the formula down column B, th
range values change and eventually my array table is off. I also can'
seem to apply the formula to all of column B at once. Do the returne
answer columns have to be next to each other? Please give layman'
answers if possible. Can someone tell me line for line what to put i
the wizard? The relationship table has three columns that coincide wit
columns A,B, and E of the main worksheet.

Thank

 

[JudithJubilee]

To stop the array moving when you copy down you need to
name the range. Highlight the data not including column
heading and click in the Name Box to the left of the
Formula Bar and give it a name. Make sure you press
Enter to confirm it. You will have to change the formula
to have the name in. This may also help your other
problems.

Hope this helps

Judith

 

[JulieD]

Hi

Okay first of all to ensure that your range doesn't change as you fill down
add some dollar signs in - e.g if your range is G1:J10 - put $G$1:$J$10 -
this will ensure that it doesn't change (known as making the values
absolute - you can either type the $ or click on G1 in the formula bar and
press F4 etc)

The other thing you need to be aware of is that the value in the first bit
of the VLOOKUP parameters MUST be the value in the first column of your
lookup table

so if your lookup table is G1:J10 and in G you have the types of different
animals and in J you have their expected lifespan - you can only use the
VLOOKUP to lookup a type of animal and return the lifespan and not the other
way around.

other than that - you didn't actually give the co-ordinates of your lookup
table in relation to the info in column A, B & E but lets assume that the
table you're looking up to are in G1:J10
and you're entering the same type of data in column A as in G
and you're getting B filled in using the following formula
=VLOOKUP(A1,$G$1:$J$10,2,false) where A1 is the thing you're looking up,
$G$1:$J$10 is the table containing the lookup value and the answers, the 2nd
column of the $G$1:$J$10 table (namely column H) contains the information
you want to see in B and you want an exact match not an approximate one
(FALSE means exact)

so to get E you need a formula like
=VLOOKUP(A1,$G$1:$J$10,4,false)
where the actual column containing the information you want in E comes from
the 4th column in the lookup table (namely J)

Hope this makes sense and helps

Cheers
JulieD

 

[Frank Kabel]

Hi
take a look at the help for 'absolute/relative references'. In your
case something like the following should work
=VLOOKUP(A1,'lookup_sheet'!$A$1:$C$1000,2,0)
and copy down