Solved i want to write a formula on page 2 to pull info from page 1

June 15, 2013 at 15:09:48
Specs: Windows 7
i want to search coulmn a for an item and then transfer content from corresponding cell in column b to page 2 what is the formula to do this

See More: i want to write a formula on page 2 to pull info from page 1

Report •


#1
June 15, 2013 at 15:38:26
Your looking for a =VLOOKUP()

With your data like:

Sheet 1

    A       B
1) 123    XYZ
2) 456    ABC
3) 789    IOU

On Sheet 2 in cell A1 enter: 456
On Sheet 2 in cell B1 enter the formula: =VLOOKUP(A1,Sheet1!A1:B3,2,0)
This will return the corresponding letters of ABC

MIKE

http://www.skeptic.com/


Report •

#2
June 16, 2013 at 03:23:01
✔ Best Answer
Thanks Mike done that one.
can you help to this over multi coulmns

A B C D E F
1) A 1 A1 29 A2 58

2) B 2 B1 30 B2 59

3) C 3 C1 31 C2 60

want to put A,A1or A2 In C1 and get the corresponding figures in D1

many thanks if you can help.


Report •

#3
June 16, 2013 at 07:33:37
Sorry, I don't understand what it is your doing?

A before & after would be helpful, also read this How To
on how to use the < PRE > Tags to post data in these forums;

http://www.computing.net/howtos/sho...

MIKE

http://www.skeptic.com/


Report •

Related Solutions

#4
June 16, 2013 at 07:34:34
johnrp2,

Before posting any more example data in this forum, please click on the following line and read the instructions found via that link. Thanks!

DerbyDad03
Office Forum Moderator

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.


Report •

#5
June 16, 2013 at 09:16:49
  
       A    B      C    D     E     F   
1      A    1     A1    29    A2    57
2      B    2     B1    30    A3    58
3      C    3     C1    31    A4    59
4      D    4     D1    32    A5    60
5      E    5     E1    33    A6    61
6      F    6     F1    34    A7    62

Above is sheet 1
i want be able to select any number
from colums b,d,f into column 4 on sheet 2

Report •

#6
June 16, 2013 at 09:38:24
    A     B     C     D     E     F

1) A 1 B 29 C 57

2) A1 2 B1 30 C1 58

3) A2 3 B2 31 C2 59

Above is sheet 1
i want to pull info from column B,D,F into column D on sheet 2


Report •

#7
June 16, 2013 at 15:19:30
Try this:


Sheet 1 looks like:

       A    B      C    D     E     F   
1      A    1     A1    29    A2    57
2      B    2     B1    30    A3    58
3      C    3     C1    31    A4    59
4      D    4     D1    32    A5    60
5      E    5     E1    33    A6    61
6      F    6     F1    34    A7    62

On Sheet 2 in cell A1 if you enter: A
On Sheet 2 in cell B1 enter the formula: =VLOOKUP(A1,Sheet1!A1:B6,2,0)
This will return the number 1 from column B

The break down of the =VLOOKUP()

First is the Lookup_Value or cell A1 which contains in this case the letter A.
A single item specified by the user.

Next is Sheet1!A1:B6 which is the Table_Array or the matrix where we first match our lookup value and from which you want to select your value from.
The Table Array can be two or more columns.

Next is the number 2, which is the Column_Index this tell us how many column to the RIGHT to count to get our match.
Since we use the number 2, we count within our matrix starting at column A as 1 column B as 2 so we select the contents of column B

Lastly is the Range_Lookup which simply says 0 for an Exact Match, or 1 for approximate match, we want an Exact Match so we use 0

Now for pulling data from column D using a match from column C:

On Sheet 2 in cell A1 enter: C1
On Sheet 2 in cell B1 enter the formula: =VLOOKUP(A1,Sheet1!C1:D6,2,0)
This will return the number 31 from column D,
since we specified the 2nd column and or matrix starts at
column C.

Now for pulling data from column F using a match from column E:

On Sheet 2 in cell A1 enter: A7
On Sheet 2 in cell B1 enter the formula: =VLOOKUP(A1,Sheet1!E1:F6,2,0)
This will return the number 62 from column F,
since we specified the 2nd column and or matrix starts at
column E.

Can you figure out what we get with this formula:

On Sheet 2 in cell A1 you enter: A
On Sheet 2 in cell B1 enter the formula: =VLOOKUP(A1,Sheet1!A1:F6,6,0)

MIKE

http://www.skeptic.com/


Report •

#8
June 16, 2013 at 16:08:37
I'm not sure if this is what you are trying to do, but if you want to enter a value that might be in Column A, C or E and return the corresponding value from B, D or F, respectively, then try this:

=IF(ISNA(VLOOKUP(A1,Sheet1!$A$1:$B$6,2,0)),
IF(ISNA(VLOOKUP(A1,Sheet1!$C$1:$D$6,2,0)),
IF(ISNA(VLOOKUP(A1,Sheet1!$E$1:$F$6,2,0)),"Value Not Found",
VLOOKUP(A1,Sheet1!$E$1:$F$6,2,0)),
VLOOKUP(A1,Sheet1!$C$1:$D$6,2,0)),
VLOOKUP(A1,Sheet1!$A$1:$B$6,2,0))

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.


Report •

Ask Question