selecting specific data

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Hi everyone

I have another little problem.

I have got a list of 20 items on sheet1, Cells A1 - A20 (numbered 1 - 20) and Cells B1 - B20 are items relating to that number, ie, number 1 = Concrete, number 2 = walls, and so on.

Now on sheet 2 I have a table, again cell range A1:B20. I would like to know a formula so that when I enter a number in to column A, the corresponding item to that number appears in column B.

ie, if I enter number 8 in Cell A1 on sheet 2, then I would like Floor to appear in Cell B1.

Does this make sense?
 
Hi Lee

Data range in Sheet1 = A1:B20 for example

In Sheet2

A1 : 8
B1 : =VLOOKUP(A1,Sheet1!A1:B20,2,FALSE)

It will look for 8 in the first column of the data range and display
the value from the second column (see the number 2 in the formula)


--
Regards Ron de Bruin
http://www.rondebruin.nl


Lee Kelly said:
Hi everyone

I have another little problem.

I have got a list of 20 items on sheet1, Cells A1 - A20 (numbered 1 - 20) and Cells B1 - B20 are items relating to that number,
Click to expand...
ie, number 1 = Concrete, number 2 = walls, and so on.
Now on sheet 2 I have a table, again cell range A1:B20. I would like to know a formula so that when I enter a number in to column
Click to expand...
A, the corresponding item to that number appears in column B.
 
One way:

In Sheet2
-------------
Put in B1:
=IF(ISNA(MATCH(A1,Sheet1!$A$1:$A$20,0)),"",VLOOKUP(A1,Sheet1!$A$1:$B$20,2,0)
)

Copy down as desired

--
Rgds
Max
xl 97
--
Please respond, in newsgroup
xdemechanik <at>yahoo<dot>com
---
Lee Kelly said:
Hi everyone

I have another little problem.

I have got a list of 20 items on sheet1, Cells A1 - A20 (numbered 1 - 20)
Click to expand...
and Cells B1 - B20 are items relating to that number, ie, number 1 =
Concrete, number 2 = walls, and so on.
Now on sheet 2 I have a table, again cell range A1:B20. I would like to
Click to expand...
know a formula so that when I enter a number in to column A, the
corresponding item to that number appears in column B.
 
Thanks guys, works a treat.

just one problem though. I copied the formula down column B on sheet 2 and where I do not have a value in A, #N/A appears. Is there any way this could remain blank?
 
Hi

=IF(ISNA(VLOOKUP(.............)),"",VLOOKUP(...............))

This will return a empty cell if the Vlookup returns a #N/A error.


--
Regards Ron de Bruin
http://www.rondebruin.nl


Lee Kelly said:
Thanks guys, works a treat.

just one problem though. I copied the formula down column B on sheet 2 and where I do not have a value in A, #N/A appears. Is
Click to expand...
there any way this could remain blank?
 
Lee Kelly said:
just one problem though. I copied the formula down column B on sheet 2
and where I do not have a value in A, #N/A appears.
Is there any way this could remain blank?
Click to expand...

um .. thought the formula suggested earlier
 
Back
Top