Array enter (enter using Ctrl-Shift-Enter) a formula like this into a cell in column A:

=SMALL(IF(3:3="Test",COLUMN(3:3)),COLUMN())

and copy to the right. This will return the column numbers that contain "Test" in row 3.

You can hide the errors by using

=IF(ISERROR(...),"",...)

where ... is the formula above.

If you want to find other values, then you can use this array formula

=INDEX(4:4,SMALL(IF(3:3="Test",COLUMN(3:3)),COLUMN ()))

which will return the values from row 4 when row 3 = "Test"

HTH,
Bernie
MS Excel MVP


wrote in message ups.com...
Is there a function to look up and return multiple columns similar to
the vlookup which returns just one column.

Thanks