look up list

[medic63]

How do i set up a worksheet to lookup a list of names from sheet 2 so I can enter a number on the page 1 worksheet that matches a name on sheet 2. I am an not excel gifted and have tried to use vlookup formula but am doing somthing wrong because i cant get the pages to link.

 

[simonf]

If your lookup data is in sheet2, try to first name the data range, then use the name in the lookup function in sheet1.

 

[lenze]

Here is an example (Range in yellow named "List") using the Named range like suggested above. Also, you can use the Format in B4 to refer to a range on another sheet.

Book1
	A	B	C	D	E
1				1	Smith
2	2	Jones		2	Jones
3				3	Brown
4	3	Brown		4	Thomas
...

This message was edited by lenze on 2002-10-08 17:15

 

[simonf]

So if your lookup range is sheet2, A1:B20 and matching sheet1 D12 to col A to return value from col B, returning value on sheet1 E12, the try:

name the range A1:B20 from sheet2 - happy
in sheet1 E12 enter:
Vlookup(D12,happy,2,FALSE)

see if it works for you

 

[SamS]

On 2002-10-08 17:07, medic63 wrote:
How do i set up a worksheet to lookup a list of names from sheet 2 so I can enter a number on the page 1 worksheet that matches a name on sheet 2. I am an not excel gifted and have tried to use vlookup formula but am doing somthing wrong because i cant get the pages to link.

Hi medic63 - the best solution is to name the list you have on sheet 2 ie highlight the list and in the name box (to the left of the formula bar) enter a range name eg namelist. On sheet 1 if you enter a number in cell A2 your formula in cell A3 would read =vlookup(A2,namelist,2,false). I am assuming that the first column is numbers, the second is names. Let us know if this doesn't work.

 

[medic63]

thanks guys it works

 

[medic63]

OK now that that worked next question is.. how do I set up the cells to remain blank when the cell for the look-up number is empty right now it shows a #N/A

second... is there a way the cells that hold the formula can be protected so the formula cannot be wiped out, the protection option seems to be for the whole worksheet

 

[lenze]

To prevent the N/A us =IF(ISBLANK(A1),"",VLOOKUP(A1,yourRange,2,0)

The protection works for cells that are locked. By default, all cells are locked. To protect the sheet and allow entry in selected cells, unlock those cells (Format>Cells>Protection Tab) before protecting the worksheet