Is this possible to do?

  • Thread starter Thread starter karim
  • Start date Start date
K

karim

Hi everyone,
I have a question. If I have 2 sheets in excel and on the 2nd
sheet I have a table that is divided in to few catagories, lets say Profile,
Item, and length. what I'm trying to do is on sheet 1, I want to type the
Item in one cell then it automatically display the Profile and the length in
to two different cells. I tried to play with the formulas a little but I
could not figure it out.
 
You will need to use an INDEX/MATCH combination - both described in
Excel Help.

Hope this helps.

Pete
 
Assuming a table on sheet 2 in this format
Profile Item Length
1 2 3
4 5 6
7 8 9

Put a 1 in a1 andd this formula in b1 and drag right to c1
=VLOOKUP($A$1,Sheet2!$A$1:$C$4,COLUMN(B1),FALSE)

for a 1 in A1 it will return 2 and 3 in B1 - C1

Mike
 
The OP wanted to enter the Item on the other sheet, Mike , then pull
Profile and Length.

Pete
 
I did try to use the help for using Index/Match but I still can't get it to
work.
what I did was, I went to Formulas, then clicked on Lookup & Reference, then
choose VLOOKUP.
and for the lookup_value I put the cell were I type the Item #
for the Table_Array I put the table I created in sheet 2 that has profile,
item, and length
and for Col_index_num and also put the table in sheet 2.
so I'm not sure if that is right or i'm missing something.
 
I wish people would give details of the layout and quantity of data on
their sheets, then the formula can be customised to suit their
circumstances without any further changes. In the absence of that,
then, let's go with Mike's suggested layout in Sheet2:

Profile Item Length
1 2 3
4 5 6
7 8 9

and in A2 of sheet1 you enter a value for the item you are tring to
find. Put this formula in B2 of that sheet to retrieve the profile:

=INDEX(Sheet2!A$2:C$4,MATCH($A2,Sheet2!A$2:C$4,0),1)

and this one in C2 to retrieve the length:

=INDEX(Sheet2!A$2:C$4,MATCH($A2,Sheet2!A$2:C$4,0),3)

Change the cell references to suit your data.

Hope this helps.

Pete
 
Sorry, slight amendment needed:

B2: =INDEX(Sheet2!A$2:C$4,MATCH($A2,Sheet2!A$2:A$4,0),1)

C2: =INDEX(Sheet2!A$2:C$4,MATCH($A2,Sheet2!A$2:A$4,0),3)

Hope this helps.

Pete
 
thank you so much.

Pete_UK said:
Sorry, slight amendment needed:

B2: =INDEX(Sheet2!A$2:C$4,MATCH($A2,Sheet2!A$2:A$4,0),1)

C2: =INDEX(Sheet2!A$2:C$4,MATCH($A2,Sheet2!A$2:A$4,0),3)

Hope this helps.

Pete
Click to expand...
 
Back
Top