Look up and return multiple columns
 

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

Thanks

Look up and return multiple columns
 

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

Look up and return multiple columns
 

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

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

Look up and return multiple columns
 

This will do it

Thank you

Look up and return multiple columns
 

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

Look up and return multiple columns
 

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