Look up and return multiple columns
I've re-thought your post, and I think you may simply want to use something like
=VLOOKUP(WhatToFind,$A$1:$H$100,COLUMN(B1),False)
and copy that to the right for as many 'columns' as you want to return.
HTH,
Bernie
MS Excel MVP
"Bernie Deitrick" <deitbe @ consumer dot org wrote in message
...
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
|