Return MULTIPLE corresponding values for ONE Lookup Value, Horizontally, in one Row? [closed]

asked 2015-10-04 18:42:22 +0200

waking dreams gravatar image

updated 2016-03-09 21:10:47 +0200

Alex Kemp gravatar image

Column A: (Cell 1 to 7) Oranges, Apples, Pears, Grapes, Apples, Oranges, Apples Column B: (Cell 1 to 7) $10, $12, $14, $18, $19, $16, $11

Column A: (Cell 11) Apples : Column B: (Cell 11) $12

I am trying to get Libreoffice Calc 5, Ubuntu 14.04 to display horizontal results from a vertical lookup. I have searched and tried different formulas for the past few days and I am stumped with this. Using the below I can get the above to display one return in Column B Cell 11. I used the CTRL SHIFT ENTER method to activate the array and formula. But the formula will not 'drag' to display multiple results horizontally! Instead I'm presented with Err:508.

Any Help would be much appreciated and thank you. This is my first question so I cannot upload an image of the sheet itself. Sorry for the poor layout.

The formula used is;

=INDEX($B$2:$B$8, SMALL(IF($A$11=$A$2:$A$8, ROW($A$2:$A$8)-ROW($A$2)+1), COLUMN(A1))) [Formula]

Here is a link to the method I used. It is the last example on the page. link Horizontal Return

edit retag flag offensive reopen merge delete

Closed for the following reason the question is answered, right answer was accepted by Alex Kemp
close date 2015-10-06 16:59:08.785124

Comments

I solved this myself. Hold CTRL while copying the formula to the adjacent cells.

waking dreams gravatar imagewaking dreams ( 2015-10-06 14:44:06 +0200 )edit

Hold Ctrl+Shift while dragging a formula onto other cells

adam1969in gravatar imageadam1969in ( 2016-09-13 10:37:44 +0200 )edit