How do I list info from one worksheet to another.....

P

P.T. in Phx

Here is my problem, I have one sheet that has info that is being listed on
another seperate sheet. I can get the info to populate the cell, but it will
put the last set of numbers in a cell if the listing is not there. Here is
the formula I am using:
=LOOKUP(A2,'Lot Check'!$B$2:$B$167,'Lot Check'!$A$2:$A$167)
I have tried vlookup and they do not seem to work. Any help would be great.

Thanks
P.T. in Phx
 
D

Dave Peterson

I think you'd want to use something like:

=vlookup(a2,'lot check'!a:b,2,false)

If the lookup key was in column A and you wanted to bring back the value from
column B.

But since your data isn't laid out in that order, you can use =index(match())

=index('lot check'!a:a,match(a2,'lot check!'b:b,0))

Debra Dalgleish's has some notes you may like:
http://www.contextures.com/xlFunctions02.html (for =vlookup())
and
http://www.contextures.com/xlFunctions03.html (for =index(match()))
 
I

Ian

LOOKUP will find the next smallest number in the range if an exact match is
not found.
VLOOKUP will do the same unless you add an optional ,FALSE before the
closing bracket. The problem with VLOOKUP in your case is that you want to
match colum B and return column A. VLOOKUP always tries to match the first
column in the array. You could use this if you can change the order of the
original data (swap columns A & B) then use

=VLOOKUP(A2,'Lot Check'!$A$2:$B$167,2,FALSE)

It will return a #N/A error if the value in A2 can not be matched. It's
untested, but should work.
 
Top