Incorporating detail of one sheet into another.

[mangesh]

There are 2 excel worksheet. I would like to incorporate detail of sheet
2 into sheet1. e.g

Sheet2 Contain the following details
A B C D E
F G
Agent1 Agent2 Agent3 Agent4 Agent5


1 Apple Cycle

2 Mango Car

3 Banana Bus

4 Coconut Motor and so on in coloumn c , d , e,f and
g.


Now what I would like to do is If in sheet 1 If I select Agent 1 all
the detail of sheet2 which has column as agent1 should appear exactly
as it is in sheet2. And the same thing for Column B, c ,d, e.

Thanks and Regards.
Mangesh Khati.

 

[Mangesh Yadav]

The following formula when entered in book1 will give you the column nos for
the lookup value in sheet2.
=MATCH(A1,Sheet2!A1:C1,0)
A1 will have Agent1, or Agent2, or... whatever your selection
Sheet2!A1:C1 has Agent1, Agent2, Agent3

Once you get the column number then you can use
=INDEX(Sheet2!A2:C4,1,MATCH(A1,Sheet2!A1:C1,0))
The '1' gives the value in the first row and the column given by
MATCH(A1,Sheet2!A1:C1,0) from the array Sheet2!A2:C4.

Mangesh