Trying to HLOOKUP a value from another sheet and display all the d

[gktan]

Hi,

Is there a way to HLOOKUP a value from SheetB and display all the result of
that value in SheetA?

EG.
SheetA:
A1
Dust
Desk
Chair

Sheet2
Dust Desk Chair
D1 OY yu
d2 or yi
d3 os yt


in SHeetA, I need to HLOOKUP "Desk" and to display all the rows in SHeet2,
the result should be
OY
or
os
in rows...

PLease let me know how do I get that???


THank you very very much in advanced.


//GK

 

[Jacob Skaria]

Try the below formula in SheetA Cell B1. Assuming you have values to be
looked up in A1 and subsequent rows..

=HLOOKUP($A$1,Sheet2!A1:Z100,2) to return 1st value

OR

=HLOOKUP($A$1,Sheet2!A1:Z100,COLUMN()) to return 1st value
copy the formula to the cells C1, D1 ,E1...

 

[Jacob Skaria]

With absolute reference....

=HLOOKUP($A$1,Sheet2!$A$1:$Z$100,2) to return 1st value
=HLOOKUP($A$1,Sheet2!$A$1:$Z$100,3) to return 1st value

OR if you are copy it to the columns C1,D1,E1.....

=HLOOKUP($A$1,Sheet2!$A$1:$Z$100,COLUMN())
and copy the formula to c1,d1,e1..........and so on

If this post helps click Yes

 

[gktan]

HI Jacob,

THanks. But do you know if I can use HLOOKUP together with COUNTA? or how
OFFSET can be used???

//GK

 

[Jacob Skaria]

Are you looking for a single formula...

To list down A2,A3,A4....use
=HLOOKUP($A$1,Sheet2!$A$1:$Z$100,ROW())

To list the items to the right in B1,C1,D1.... use
=HLOOKUP($A$1,Sheet2!$A$1:$Z$100,COLUMN())

If this post helps click Yes