vlookup

  • Thread starter Thread starter politesse
  • Start date Start date
P

politesse

I use vlookup to get information listed on worksheet 1 (by a numerical
number say 1 to 56) in column A and enter it on worksheet 2.

I don't want worksheet 2 to change if I add Information to worksheet 1 and
then sort worksheet 1 by column B.

can this be done

Thanks
 
You are using just three parameters. Have a look at Help to learn about the
fourth one. I think this could solve your problem
best wishes
 
maybe you are misunderstanding my problem.

What I am doing is I have a worksheet with a listing of all the people I
send checks to, I can enter the number of the person and the amount in two
differns boxes and then run a macro that will print the check and then go to
another sheet where I register the check I printed.

If I then enter the same number there it will look up the person on the
first worksheet all I have to then enter is the date and amount. (so far so
good)

Now if I want to add an new person to the list and sort it will change the
register, which I do not want.
 
Hi

If you are adding to the register, then you will either need to use a
dynamic range for your Vlookup, or give the whole of column And B as the
range, otherwise your formula will not be looking at all eligible rows.
On sheet Enter, Insert>Name>Define>Name Customers > Refers to
=OFFSET(ENTER!$A$1,0,0,COUNTA(Enter!$A:$A),2)

Then use
=VLOOKUP(B1091,Customers,2,0)

Alternatively, without a named range
=VLOOKUP(B1091,Enter!A:B,2,0)

Note in both cases, I have added the 4th parameter as 0 (or False) exactly
as Bernard suggested,
 
I think the problem was misunderstood, my question has nothing to do with
using vlookup I have no problem with this.

What I am looking for if there is a way to lock, format or change the
vlookup before sorting the list so it won't change. with the sorted table.

In other words if the table was deleted I want the lookup not to error or
change, Is there something I can add to my sort macro that will do this.

I did find use for the forth parameter, thanks
 
Hi

I'm sorry but I'm not understanding your problem.
If the column being looked up is being .sorted along with the rest of the
table, then there will be no change in the values returned by your Vlookup
formula after sorting, compared with before sorting.

If the content of B1091 was Roger, and Roger occurred as the 20th items in
the column A of the table being looked up, with a value of
100 in column B of the same row, your result would be 100.

If you now sorted the table in Reverse order, so that Roger was the 3rd item
in Column A, then the associated value of 100 should be the 3rd items in
column B and would be returned by your formula as before.

Are you sure that you are marking columns A and B to be sorted, and not just
column A?
 
I think you are now understand my problem, I am trying to sort the alpha
names in order and keep the numbers in order. this changes the lookup up
answer, as I see you are beginning to understand.

So what I am looking for is a way to lock in the looked up items with a
macro (which could be part of the sort macro) before I sort.

Right now I have only 56 names in the table so I guess I could forget about
sorting them.

My thinking I know you could change formula to values, and was looking for
something like that.

I really appreciate the help. And sorry for all the confusion
 
No, If you sort column A and Column B together, but choose Sort on A, then
the values will stay with the names and you won't have a problem.


If you are saying you want to permanently "fix" values that have already
been looked up, and destroy the formula in those cells, then you could
Select the range and Paste Special Values back over the range to fix the
data.
 
Sometimes you cant see the forest because of the trees

Copy and paste value is what I was looking for, cant believe I missed it.

Think I will go hide in the closet

Thanks all
 
Sometimes you cant see the forest because of the trees
Very often, in my case. Sorry it took me so long to suggest it.
 
Back
Top