New to VLookUps.... please advise...

[neilcarden]

Hi all, struggling a little... I'm new to the VLookup function an
need some help.
I know this might be fairly simple - so if someone could help o
recommend a good book or resource, I'd be very grateful

Please see example

http://www.neilweb.co.uk/example.xl

Hi, this is what I'd like to do
I have a predefined template on Sheet 1 which stays in the same orde
and format
I import a report into Sheet 2 which has all the above names an
more
However I only need the data from the above names to bring across fro
Sheet 2 to Sheet 1

Thanks very much in advance..
Nei

 

[Nick Hodge]

Neil

If the names entry you are making is in column A on sheet1 (Starting in A1)
and the data in Sheet2 goes from A1100, with the address (for example) in
column B on Sheet2.

Then in B1 on sheet 1 enter

=VLOOKUP(A1,Sheet2!$A$1:$D$100,2,FALSE)

This looks up the value in A1 on sheet1 (Name) and compares in with the
left-most column in the 'Lookup range' on sheet2 (Column A). When it finds
it, it will return the value 2 cells to the right (The name column is no.1).
The FALSE parameter, simply returns only exact matches or an error

--
HTH
Nick Hodge
Microsoft MVP - Excel
Southampton, England
(e-mail address removed)

 

[Max]

In Sheet1
-------------
Put in C4: =VLOOKUP(TRIM(B4),Sheet2!$B$3:$C$11,2,0)
Copy down to C10
This should return the correct values in C4:C10

Or, perhaps better with an error-trap to return blanks: ""
instead of ugly #NAs for unmatched cases, put instead in C4:

=IF(ISNA(MATCH(TRIM(B4),Sheet2!$B$3:$B$11,0)),"",VLOOKUP(TRIM(B4),Sheet2!$B$
3:$C$11,2,0))

Copy down to C10 as before

TRIM(..) is used around the lookup values (which are text in your case) in
the formula to improve the robustness of matching, in case there's any
extraneous spaces (these may not be apparent) accidentally keyed-in the
lookup values in B4:B10

 

[Guest]

Add this formula to cell C4 of sheet 1. Copy it down the lookup range in
sheet 1.

=VLOOKUP(B4,Sheet2!$B$3:$C$11,2,FALSE)

If the formula,

B4 - refers to the value you want to find.
Sheet2!$B$3:$C$11 - refers to the lookup range on sheet 2
2 - refers to the cell offset. Excel always starts at the left side of the
lookup range. Since your range is Sheet2!$B$3:$C$11, Excel starts in column
B, finds the value, and then moves over to column C to get the value (2
columns total).