CODEWITHSUNDEEP
vbaexcelexcel-formulalookup
AayushmanR
AayushmanR

Reputation: 63

How to get multiple values with a single look up in Excel with transposing the data from column to rows

How do I get multiple values with a single look up in Excel with transposing the data from column to rows.

Please see below image for how data is.

Input data:

enter image description here

Output data:

enter image description here

Upvotes: 0

Views: 127

Answers (2)

Tom Sharpe
Tom Sharpe

Reputation: 34210

Alternative using INDEX/ROWS

=IFERROR(INDEX($A$2:$A$5,INT((ROWS(A$1:A1)-1)/5)+1),"")

for the name

=IFERROR(INDEX($B$2:$F$5,INT((ROWS(A$1:A1)-1)/5)+1,MOD(ROWS(A$1:A1)-1,5)+1),"")

for the number.

enter image description here

Upvotes: 3

Mrig
Mrig

Reputation: 11702

Assuming your data is in Range A2:F5 and you want output to be displayed from Cell H2 (see image from reference),

Enter following formula in Cell H2

=OFFSET($A$2,FLOOR((ROW(A2)-ROW($A$2))/5,1),0)

drag/copy down as required.

Enter the following formula in Cell I2

=OFFSET($B$2,FLOOR((ROW(B2)-ROW($B$2))/5,1),MOD(ROW(A2)-ROW($A$2),5))

drag/copy down as required.

enter image description here

Upvotes: 3

Related Questions