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

Thanks

Where will you store the n columns returned?

--
AP

a écrit dans le message de
ups.com...
Is there a function to look up and return multiple columns similar to
the vlookup which returns just one column.

Thanks

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

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

This will do it

Thank you

Hi Bernie,

I have been looking for an Excel fn to look in the first column of a
table and return the following column entries when it finds a match.
Unforunately my table has duplicate entries in column 1 and vlookup
will only return one row. For example, I want to get out all the rows
with "Ben" in the first column and paste them to a new worksheet.

Can you pls pls steer me in the right direction? Im doing my head in :(

Cheers in Advance I hope

BEn



Bernie Deitrick wrote:

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

I suggest DataFilterAdvanced filter

HTH
--
AP

"BenGenic" a écrit dans le message de
oups.com...
Hi Bernie,

I have been looking for an Excel fn to look in the first column of a
table and return the following column entries when it finds a match.
Unforunately my table has duplicate entries in column 1 and vlookup
will only return one row. For example, I want to get out all the rows
with "Ben" in the first column and paste them to a new worksheet.

Can you pls pls steer me in the right direction? Im doing my head in :(

Cheers in Advance I hope

BEn



Bernie Deitrick wrote:

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