Spreadsheet formula question!

[cindy]

I am working on a spreadsheet and I want to enter a number from a table and
have the associated values from the table transfer with the number into my
spreadsheet. What functions and formulas should I use? (Below is the Table,
the PTM# is the one I would query for.)

X L/R Y PTM#
0.54 R 0.58 20
0.82 R 0.50 21
0.66 R 0.73 22
0.06 L 0.27 23
0.03 R 0.13 24
0.55 R 0.29 25

 

[JE McGimpsey]

Take a look at VLOOKUP in XL Help. For example, if your lookup value is
in J1, and your table is in A, then:

=VLOOKUP(J1, A, 4, FALSE)

 

[William Horton]

I would use the VLOOKUP function. You are going to have to move the PTM#
column to the far left of your table however (column A). Then lets say you
want the values to transfer to sheet2 and that you enter the PTM# in column A
of sheet 2. The formula would be like the following.

Assuming your first table is in cells Sheet1!$A$1:$D$7

=VLOOKUP(A1, Sheet1!$A$1:$D$7,2,0) Put in column B
=VLOOKUP(A1, Sheet1!$A$1:$D$7,3,0) Put in column C
=VLOOKUP(A1, Sheet1!$A$1:$D$7,4,0) Put in column D

Hope this helps.

Bill Horton

 

[Arvi Laanemets]

Hi

When your transfer table is p.e. on sheet MySheet in range MySheet!C2:F100,
and you want to look for a value in column F associated with a value in
column C, (you look for a value in PTM# column on row where p.e. X=0.03),
then
=VLOOKUP(0.03, MySheet!$C$2:$F$100,4,0)

The formula looks for value 0.03 in first column of referred range, and
returns a value from 4th column of this range, when there is an exact match.
When there is no exact match, an error is returned. To avoid the error, you
can use VLOOKUP with 1 as 4th parameter (then the nearest match is returned,
but your lookup table must be sorted on 1st column to get resonable results
at all), or you use an error traping:
=IF(ISNA(VLOOKUP(....)),"",VLOOKUP(.....))

 

[ah]

Hi,

I am assuming you are in worksheet called Sheet 1 and that you will be using
a second spreadsheet to transfer this data into (sheet 2) and that you
started in cell A1. Firstly put column D (PTM#) as your first coulmn, it
makes vlookups a lot easier then use this formula =VLOOKUP($A2,'sheet
1'!$A$2:$D$7,COLUMN('sheet 2'!B1),FALSE) then drag accroos the clees as needed

thanks

ah