Look up and return multiple columns

[aronfel@gmail.com]

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

Thanks

[Ardus Petus]

Where will you store the n columns returned?

--
AP

<aronfel@gmail.com> a écrit dans le message de
news:1141834865.268496.70370@v46g2000cwv.googlegroups.com...
> Is there a function to look up and return multiple columns similar to
> the vlookup which returns just one column.
>
> Thanks
>

[Bernie Deitrick]

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


<aronfel@gmail.com> wrote in message news:1141834865.268496.70370@v46g2000cwv.googlegroups.com...
> Is there a function to look up and return multiple columns similar to
> the vlookup which returns just one column.
>
> Thanks
>

[Bernie Deitrick]

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
news:u6VSuVtQGHA.516@TK2MSFTNGP15.phx.gbl...
> 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
>
>
> <aronfel@gmail.com> wrote in message news:1141834865.268496.70370@v46g2000cwv.googlegroups.com...
>> Is there a function to look up and return multiple columns similar to
>> the vlookup which returns just one column.
>>
>> Thanks
>>
>
>

[aronfel@gmail.com]

This will do it

Thank you

[BenGenic]

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
>
>
> <aronfel@gmail.com> wrote in message news:1141834865.268496.70370@v46g2000cwv.googlegroups.com...
> > Is there a function to look up and return multiple columns similar to
> > the vlookup which returns just one column.
> >
> > Thanks
> >

[Ardus Petus]

I suggest Data>Filter>Advanced filter

HTH
--
AP

"BenGenic" <benv@allcomnetworks.com.au> a écrit dans le message de
news:1142573838.980529.284890@u72g2000cwu.googlegroups.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
> >
> >
> > <aronfel@gmail.com> wrote in message
news:1141834865.268496.70370@v46g2000cwv.googlegroups.com...
> > > Is there a function to look up and return multiple columns similar to
> > > the vlookup which returns just one column.
> > >
> > > Thanks
> > >
>