VLOOKUP to extract multiple results - without vba

tinferns

Board Regular
Joined
Aug 18, 2009
Messages
122
Office Version
  1. 2019
  2. 2016
Platform
  1. Windows
Now, here's a tough one.... I am trying the below 2 formula's to obtain multiple results using index & Match formula. Doesn't seem to work. I am using Excel 2019 and Office 365. I want to avoid using VBA.

Formula's (options) used:
Index + Match1 = {=INDEX($B$3:$B$988,SMALL(IF(D3=$A$3:$A$988,ROW($A$3:$A$988)-ROW($A$3)+1),ROW(2:2)))}

Index + Match2 = {=INDEX($B$3:$B$987,SMALL(IF((D3=$A$3:$A$987),MATCH(ROW($A$3:$A$987),ROW($A$3:$A$987)),""),ROWS($C$2:C2)))}

Below is a screenshot of the above activity. Please help...

Thanks....
 

Attachments

  • Index n Match1.png
    Index n Match1.png
    69.3 KB · Views: 10

Some videos you may like

Excel Facts

Can you AutoAverage in Excel?
There is a drop-down next to the AutoSum symbol. Open the drop-down to choose AVERAGE, COUNT, MAX, or MIN

tinferns

Board Regular
Joined
Aug 18, 2009
Messages
122
Office Version
  1. 2019
  2. 2016
Platform
  1. Windows
Hi Peter SSs.. i tried adding or installing XL2BB add in.. not able to find it. This is too complex.. is there anyother way out so that I can share my document with you. ?
 

Attachments

  • xl2bb.png
    xl2bb.png
    47.4 KB · Views: 4

tinferns

Board Regular
Joined
Aug 18, 2009
Messages
122
Office Version
  1. 2019
  2. 2016
Platform
  1. Windows
Guess.. this is it..

Index n Match.xlsx
ABCDEF
1RAW DATAOutcome
2DateDataDateIndex + Match1Index + Match2
301-01-2020FORWARD01-01-202055+20+20+80+65+60+75)FORWARD
401-01-202055+20+20+80+65+60+75)01-01-2020#NUM!55+20+20+80+65+60+75)
502-01-20202819257677102-01-2020BDTECH-PG
602-01-2020Bank/MOPSDOAG02-01-2020WALLETBD
702-01-2020TECH-PG02-01-2020BILLWALLET
802-01-2020BD02-01-2020GUYSBILL
902-01-2020WALLET02-01-2020CARDGUYS
1002-01-2020BILL02-01-2020CARDCARD
1102-01-2020GUYS02-01-2020CCCARD
1202-01-2020CARD02-01-2020NAGARCC
1302-01-2020CARD02-01-2020#NUM!NAGAR
1403-01-2020 BIL/DEBIT/2020010301022303-01-2020#NUM!#NUM!
1503-01-2020CARD03-01-2020#NUM!#NUM!
1603-01-2020CARD03-01-2020#NUM!#NUM!
1703-01-2020WALLET03-01-2020#NUM!#NUM!
1804-01-20202822906727804-01-2020#NUM!#NUM!
1904-01-2020BELT04-01-2020#NUM!#NUM!
2004-01-2020CARD04-01-2020#NUM!#NUM!
2104-01-2020CC04-01-2020#NUM!#NUM!
2204-01-2020APPLE04-01-2020#NUM!#NUM!
2304-01-2020MICROSOFT04-01-2020#NUM!#NUM!
2404-01-2020CC04-01-2020#NUM!#NUM!
2504-01-2020WALLET04-01-2020#NUM!#NUM!
2605-01-2020KUMA/BHARATP05-01-2020#NUM!#NUM!
2705-01-2020CC05-01-2020#NUM!#NUM!
2805-01-2020CC05-01-2020#NUM!#NUM!
2905-01-2020MALL05-01-2020#NUM!#NUM!
3005-01-2020CC05-01-2020#NUM!#NUM!
3104-01-20202824034496604-01-2020#NUM!#NUM!
3207-01-20202827242079607-01-2020#NUM!#NUM!
3307-01-2020BOX.07-01-2020#NUM!#NUM!
3408-01-2020WALLET08-01-2020#NUM!#NUM!
3508-01-2020TWEET08-01-2020#NUM!#NUM!
3602-01-2020CC02-01-2020#NUM!#NUM!
3702-01-2020NAGAR02-01-2020#NUM!#NUM!
3809-01-2020E09-01-2020#NUM!#NUM!
3909-01-2020Rana09-01-2020#NUM!#NUM!
4009-01-20202830370768409-01-2020#NUM!#NUM!
4109-01-2020PG09-01-2020#NUM!#NUM!
4209-01-20202830381852009-01-2020#NUM!#NUM!
4310-01-20202833237120110-01-2020#NUM!#NUM!
4411-01-202011-01-2020#NUM!#NUM!
4511-01-2020267.5111-01-2020#NUM!#NUM!
4611-01-2020ONLY11-01-2020#NUM!#NUM!
4711-01-20202834751604111-01-2020#NUM!#NUM!
4811-01-20202834419025311-01-2020#NUM!#NUM!
4911-01-2020WALLET11-01-2020#NUM!#NUM!
5012-01-2020Mercha/BHARATP12-01-2020#NUM!#NUM!
5112-01-2020CARD12-01-2020#NUM!#NUM!
5212-01-2020Help12-01-2020#NUM!#NUM!
5312-01-20202835860506112-01-2020#NUM!#NUM!
5412-01-20202836341170712-01-2020#NUM!#NUM!
5513-01-20202836939473313-01-2020#NUM!#NUM!
5613-01-20202837784559613-01-2020#NUM!#NUM!
5714-01-2020zip/9900619235@okbi14-01-2020#NUM!#NUM!
5814-01-2020HOUS14-01-2020#NUM!#NUM!
5915-01-2020CC15-01-2020#NUM!#NUM!
6015-01-2020CARD15-01-2020#NUM!#NUM!
6115-01-2020WALLET15-01-2020#NUM!#NUM!
6217-01-2020BD17-01-2020#NUM!#NUM!
6317-01-202017-01-2020#NUM!#NUM!
6418-01-2020 PAYU-GROFERS-PG18-01-2020#NUM!#NUM!
6518-01-2020PG18-01-2020#NUM!#NUM!
6618-01-202018-01-2020#NUM!#NUM!
6718-01-2020CC18-01-2020#NUM!#NUM!
6818-01-2020ONLY18-01-2020#NUM!#NUM!
6918-01-20202845834857218-01-2020#NUM!#NUM!
7019-01-2020₹273.40 SUCCESS19-01-2020#NUM!#NUM!
7119-01-2020WALLET19-01-2020#NUM!#NUM!
7219-01-2020WALLET19-01-2020#NUM!#NUM!
7320-01-2020MUMBAI20-01-2020#NUM!#NUM!
7423-01-2020PG23-01-2020#NUM!#NUM!
7523-01-20202853680323723-01-2020#NUM!#NUM!
7623-01-2020SHOP23-01-2020#NUM!#NUM!
7725-01-2020CC25-01-2020#NUM!#NUM!
7825-01-20202856784303625-01-2020#NUM!#NUM!
7925-01-20202856781832825-01-2020#NUM!#NUM!
8025-01-2020WALLET25-01-2020#NUM!#NUM!
8126-01-20202857917206926-01-2020#NUM!#NUM!
8226-01-20202857777311626-01-2020#NUM!#NUM!
8326-01-202026-01-2020#NUM!#NUM!
8426-01-2020WALLET26-01-2020#NUM!#NUM!
8529-01-2020BIRTHDAY29-01-2020#NUM!#NUM!
8631-01-2020Jan31-01-2020#NUM!#NUM!
8721-01-2020PL-INDIA21-01-2020#NUM!#NUM!
8801-02-20202866658506101-02-2020#NUM!#NUM!
8901-02-20202867164885401-02-2020#NUM!#NUM!
9001-02-2020CARD01-02-2020#NUM!#NUM!
9101-02-2020ACCOUNT- UPI/003215200697/Med/rameshbillava01/Can01-02-2020#NUM!#NUM!
9201-02-20202867211196201-02-2020#NUM!#NUM!
9301-02-2020537/0146801-02-2020#NUM!#NUM!
9401-02-2020NAGAR01-02-2020#NUM!#NUM!
9501-02-20202867429756401-02-2020#NUM!#NUM!
9601-02-20202867463411101-02-2020#NUM!#NUM!
9701-02-2020Mercha/BHARATP01-02-2020#NUM!#NUM!
9802-02-20202868149471602-02-2020#NUM!#NUM!
9902-02-2020ID 407-9334553-637076302-02-2020#NUM!#NUM!
10002-02-2020ID 407-9964547-461392002-02-2020#NUM!#NUM!
Sheet1
Cell Formulas
RangeFormula
E3:E100E3=INDEX($B$3:$B$988,SMALL(IF(D3=$A$3:$A$988,ROW($A$3:$A$988)-ROW($A$3)+1),ROW(2:2)))
F3:F100F3=INDEX($B$3:$B$987,SMALL(IF((D3=$A$3:$A$987),MATCH(ROW($A$3:$A$987),ROW($A$3:$A$987)),""),ROWS($C$2:C2)))
Press CTRL+SHIFT+ENTER to enter array formulas.
Cells with Conditional Formatting
CellConditionCell FormatStop If True
D3:D987Expression="t6>=1000"textNO
A3:A1326Expression="t6>=1000"textNO
 

Peter_SSs

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
48,400
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

Guess.. this is it..
Indeed it is. (y)
The only thing is I would like to see the results that you do want (with explanation) in columns D:F, not the results you don't want. ;)
 

tinferns

Board Regular
Joined
Aug 18, 2009
Messages
122
Office Version
  1. 2019
  2. 2016
Platform
  1. Windows
Thanks Peter SSs. My expectations are as below:

1) I want all the results from column B to show up in Column D or F. One challenge is that you may find 01/01/20 between row 5 - 10 and randomly in say row 250 !. So here I want all 11 outputs.
2) Lets use the same example. The date 01/01/20 appears only 11 times. However, in column D I put that date 12 times, I want the output to be either 0 or - for the 12th entry.

Guess, this is do'able. Please let me know if you need more information. Thanks..
 

Peter_SSs

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
48,400
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

I don't understand your written description of your requirements. Can you show the expected results(or at least several of them) for the sample data given and explain in relation to those examples?
 

tinferns

Board Regular
Joined
Aug 18, 2009
Messages
122
Office Version
  1. 2019
  2. 2016
Platform
  1. Windows
Sure Peter SSs. Below example I have done manually.

What you will observe:
1) In outcome section, dates are not placed in a proper sequence. I have highlighted in orange to show the mix. I am not concerned about hierarchy and sequence of data in output. As long its all there.
2) In Raw Data there are only 3 entries for 01/01/20. However in Outcome, we are seeking information for 4 entries of 01/01/20 - thus the 4th entry gets a 0
3) In raw data there are 6 entries for 04/01/20. However in outcome we are seeking only 4 entries. Thus Bangkok & Pattaya is missed out in outcome. Which is fine.

The above 2 formula's are fine, however in some cases I am getting errors. I hope the above explanations should do. Please advise if you want me to deeper.

Thanks,

Index n Match.xlsx
ABCDE
1RAW DATAOutcome
2DateDataDateIndex + Match1
301-01-2020Boston01-01-2020Boston
401-01-2020New Orleans01-01-2020New Orleans
502-01-2020Paris02-01-2020Paris
602-01-2020Frankfurt02-01-2020Frankfurt
702-01-2020Amsterdam02-01-2020Amsterdam
802-01-2020Prague02-01-2020Prague
902-01-2020London02-01-2020London
1002-01-2020Athens02-01-2020Athens
1102-01-2020Geneva02-01-2020Geneva
1202-01-2020Manchester02-01-2020Manchester
1302-01-2020Brussels03-01-2020Melbourne
1403-01-2020Melbourne03-01-2020Sydney
1503-01-2020Sydney03-01-2020Cairns
1603-01-2020Cairns04-01-2020Singapore
1703-01-2020Queensland04-01-2020Hongkong
1804-01-2020Singapore01-01-2020Chicago
1904-01-2020Hongkong04-01-2020Bejeing
2004-01-2020Bangkok04-01-2020Bali
2104-01-2020Bejeing01-01-20200
2204-01-2020Bali02-01-2020Brussels
2304-01-2020Pattaya02-01-2020Cairo
2401-01-2020Chicago02-01-2020Moscow
2502-01-2020Cairo03-01-2020Queensland
2602-01-2020Moscow03-01-20200
Sheet2
Cells with Conditional Formatting
CellConditionCell FormatStop If True
A3:A26,D3:D26Expression="t6>=1000"textNO
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
55,526
Office Version
  1. 365
Platform
  1. Windows
How about
+Fluff v2.xlsm
ABCDE
1RAW DATAOutcome
2DateDataDateIndex + Match1
301/01/2020Boston01/01/2020Boston
401/01/2020New Orleans01/01/2020New Orleans
502/01/2020Paris02/01/2020Paris
602/01/2020Frankfurt02/01/2020Frankfurt
702/01/2020Amsterdam02/01/2020Amsterdam
802/01/2020Prague02/01/2020Prague
902/01/2020London02/01/2020London
1002/01/2020Athens02/01/2020Athens
1102/01/2020Geneva02/01/2020Geneva
1202/01/2020Manchester02/01/2020Manchester
1302/01/2020Brussels03/01/2020Melbourne
1403/01/2020Melbourne03/01/2020Sydney
1503/01/2020Sydney03/01/2020Cairns
1603/01/2020Cairns04/01/2020Singapore
1703/01/2020Queensland04/01/2020Hongkong
1804/01/2020Singapore01/01/2020Chicago
1904/01/2020Hongkong04/01/2020Bangkok
2004/01/2020Bangkok04/01/2020Bejeing
2104/01/2020Bejeing01/01/2020-
2204/01/2020Bali02/01/2020Brussels
2304/01/2020Pattaya02/01/2020Cairo
2401/01/2020Chicago02/01/2020Moscow
2502/01/2020Cairo03/01/2020Queensland
2602/01/2020Moscow03/01/2020-
Main
Cell Formulas
RangeFormula
E3:E26E3=IFERROR(INDEX($B$3:$B$26,AGGREGATE(15,6,(ROW($B$3:$B$26)-ROW($B$3)+1)/($A$3:$A$26=D3),COUNTIF(D$3:D3,D3))),"-")
 
Solution

tinferns

Board Regular
Joined
Aug 18, 2009
Messages
122
Office Version
  1. 2019
  2. 2016
Platform
  1. Windows
Thanks Fluff.. That Formula did the trick.. and did it well. Thanks a ton..
 

Watch MrExcel Video

Forum statistics

Threads
1,127,529
Messages
5,625,349
Members
416,096
Latest member
forevans

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top