Lookup Columns to Rows

[Dennis]

Using XL 2000

What function(s) is best to display to do the following:


(Sheet 1) (Sheet 2)
Row Col-> A B Row Col->A D E F

4 Data 1 aa 10 Data 4
5 Data 2 bb 11 Data 1 aa cc ee
6 Data 1 cc 12 Data 2 bb dd
7 Data 2 dd 13 Data 3
8 Data 1 ee 14 Data 8

Column A on both sheets has common data that can
be "looked up."


That said how best to convert data from column to row AND
to "list" horizonally all instances of Sheet 2 Col A (Rows
10, 11, 12, 13, 14) on Sheet 1 Column A data? (Lookup
function will find first instance not all instances -
correct?)

TIA Dennis

 

[Frank Kabel]

Hi
Hi
try the following array formula (entered with CTRL+SHIFT+ENTER) in cell
D10
=IF(ISERROR(INDEX('sheet1'!$B$1:$B$10,SMALL(IF('sheet1'!$A$1:$A$10=$A10
,ROW('sheet1'!$A$1:$A$7)),COLUMN()-3))),"",INDEX('sheet1'!$B$1:$B$10,SM
ALL(IF('sheet1'!$A$1:$A$10=$A10,ROW('sheet1'!$A$1:$A$7)))
and copy this down and to the right

 

[Dennis]

Frank, thanks for your knowledge and time!

To the very end of the formula, I had to add another argument because the formula, as displayed
below in your reply, needed another argument.

Did I do what you intended? In other words, is "Column()-3" the correct missing argument?

........)),COLUMN()-3)))

As of now, the formula is:

=IF(ISERROR(INDEX(sheet1!$B$1:$B$10,SMALL(IF(sheet1!$A$1:$A$10=$A10,ROW(sheet1!$A$1:$A$7)),COLUMN()-3))),"",INDEX(sheet1!$B$1:$B$10,SMALL(IF(sheet1!$A$1:$A$10=$A10,ROW(sheet1!$A$1:$A$7)),COLUMN()-3)))


Thanks again! Dennis

 

[Frank Kabel]

Hi
yes this is the missing argument. Sorry, copy+paste error!