how do you copy a formula and keep the lookup array constant

G

Guest

when i copy an isna(match(lookup_value,lookup_array,match type)) formula, the
array changes. i'm trying to lock the lookup array and allow the lookup
value to change. any suggestions
 
N

Niek Otten

(At least) two options:

a. use $A$1:$C$9 format for your array in your formula
b. Select your array. Insert>Name>Define...Type a neme and use that name in
your formula


--

Kind Regards,

Niek Otten

Microsoft MVP - Excel
 
P

Peo Sjoblom

Use mixed absolute and relative references

=VLOOKUP(A1,Sheet2!$A$2:$C$200,2,0)

as an example will increment A1 but leave the lookup table intact

Regards,

Peo Sjoblom
 
G

Guest

Thanks Peo. Problem solved.

Peo Sjoblom said:
Use mixed absolute and relative references

=VLOOKUP(A1,Sheet2!$A$2:$C$200,2,0)

as an example will increment A1 but leave the lookup table intact

Regards,

Peo Sjoblom
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

How do I reference certain rows within array formulae? 11
Lookup Function 1
Vlookup - need the array to be constant 2
Return an array based on a lookup 2
VLOOK up returning #N/A even though value exists in lookup array 1
Reverse a function to return the array? 4
Vlookup and retrieve values 1 row below the normally retrieved val 2
Array formulas/selecting data 2

Top