Vlookup but return multiple entries?

[MottoK]

Hi,

How can I do a vlookup (equivalent) but return multiple entries e.g:

Value to search for is: ACQ

Col A Col B
ACQ 22
BBD 31
CCF 55
ACQ 44

The result would be the following list:

22
44

I need to use formulas for this not VBA if possible.

Any help VERY mcuh appreciated.

 

[MottoK]

Sorry also meant to add (and I suppose this is critical) that I only want to return the value once e.g.

Col A Col B
ACQ 22
BBD 31
CCF 55
ACQ 44
ACQ 22

The above list contains '22' twice for ACQ but I would only want to return the following:

22
44


Thanks.

 

[xlbo]

you're gonna need VBA....

Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244 before you ask a question

 

[MottoK]

Is there not a way to do this with offsets and the like?

If not can someone please show me a simple VBA method to do this?

Thanks.

 

[Fenrirshowl]

Something neat requires VBA. Using only functions you can add several columns to do the job and then hide the ones that don't show the result you need. Following works for numbers in column B.

Step 1: Use an IF statement to pull out only the values that match the criteria you specify. e.g. cell C1 "=IF(A1="ACQ",B1,"")"

Step 2: Order column C. D1= "=IF(ROW()<=COUNTIF(C:C,">0"),SMALL(C:C,ROW()),"")" Note you can just use the SMALL function

Step 3: Pull through values only once. E1 "=D1", E2 and below "=IF(D2<>D1,D2,"")"

Step 4: In column F, reorder column E using the same idea as in Step 2.

Step 5: Hide columns C to E.
Fen

 

[Rofeu]

Hi,

use the following array formula (ctrl-shft-enter):

=IF(COUNTIF($A$1:$A1,$A1)>COUNTIF($F$1:$F$6,$A1),"",INDEX($G$1:$G$6,SMALL(IF($F$1:$F$6=$A1,ROW($F$1:$F$6),""),COUNTIF($A$1:$A1,$A1)),1))

Lookup_Values in Column A
Lookup_Table in Column F
Return_Values in Column G

Cheers,

Roel

 

[combo]

If some side data/formatting is not a big mess - use pivot table. 'Col A' as page field, 'Col B' as row field and as data field. You can remove subtotals to clear table.

combo

 

[MottoK]

Many thanks - I'm having a look at all of these but in the last couple of hours I have managed to get two DISTICT columns to work with e.g.

Col A Col B
ACQ 12
BBN 55
EER 44
YUU 32
ACQ 77
ACQ 66

i.e. you will never get the same value for ACQ or BBN or EER etc twice.

NOW - all I need to be able to do is put the values in col B (which match the criteria you are searching for in col A) in to a several rows, one beneath the other (in this case I have 12 rows on another tab as there will never be more than 12 different values in col B).

There must be an easy way of doing this or at least with a formula of some sort? PLease help!

Thanks.

 

[Gavona]

AutoFilter on column A would show only the values that match.

Advanced Filter using the value you are looking up as the criteria could do the same thing. OR it could be set to put the list of matched values in another location, including another sheet, AND it could be set not to include duplicates.

I have in the past created a spreadsheet that held the data on sheet1. The user opened sheet2 and under the department heading selected the desired department from a drop down list. As soon as that was done all the records for that department appeared below "magically". If they then entered a commodity using that drop down list then the extract was refreshed to only show the info for that Department and that commodity.

How it worked: On sheet 2 there were some column headings that one wanted to filter on (Say Department and Commodity). The cells below the column headings used a data validation list so there was a dropdown list of possible values. The whole area acted as the criteria range for Advanced Filter and the (simple) advanced filter macro was activated by the event of changing the criteria.

Below the criteria area were the headings for the data one wanted to extract from sheet1 if it matched the criteria. These formed the extract range for the advanced filter.

Not sure if that approach could help in your case as the overall objective is not clear.

Regards,

Gavin

 

[yogia]

Hi Mottok:

Let me see if I understood you correctly ...

Would this work for you?

I will also post a solution using AdvancedFilter.

Yogi Anand, D.Eng, P.E.
Energy Efficient Building Network LLC

http://www.energyefficientbuild.com

 

[yogia]

Hi Mottok:

Following is a solution using Advanced Filter ...

Yogi Anand, D.Eng, P.E.
Energy Efficient Building Network LLC

http://www.energyefficientbuild.com

 

[yogia]

Hi Mottok:

Following is a solution using the FREQUENCY function ...

Copy the array formula in cell D2 to cells D36.

Yogi Anand, D.Eng, P.E.
Energy Efficient Building Network LLC

http://www.energyefficientbuild.com