VBA Index Match with 2 criteria

Revier

New Member
Joined
Nov 9, 2018
Messages
10
Office Version
  1. 2016
Platform
  1. Windows
Hi All

My brain is struggling to wrap itself around index and matching with 2 criteria where one criteria is exact match and the other is less than

My dataset (apologies its not a mini sheet, work computer restricting the download)
A​
B​
C​
800101X1
24/07/2020
800102X1
30/07/2020
800111X105/08/2020
800101X201/09/2020
800110X105/05/2020
800106X311/12/2020

What I want to achieve is to match column A to exactly "800101" then match Column C to nearest date to <Today and return the corresponding value from column B.

Iv tried using this formula, but it only returns the B column value that is closest to today and doesn't factor in the match for 800101 in column A

Excel Formula:
=INDEX(B2:B20, MATCH(TODAY(),C2:C20, 1),MATCH(800101,A2:A20,0))

I was then hoping to use VBA to replicate the index and match using worksheetfunctions

I would really appreciate Any advice you can give me.
 

Excel Facts

Do you hate GETPIVOTDATA?
Prevent GETPIVOTDATA. Select inside a PivotTable. In the Analyze tab of the ribbon, open the dropown next to Options and turn it off
What version of Excel are you using?

I suggest that you update your Account details (or click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)
 
Upvote 0
Thanks for that, how about
+Fluff 1.xlsm
ABCDEF
1
2800101X124/07/2021800101X2
3800102X130/07/2020
4800111X105/08/2020
5800101X201/09/2020
6800110X105/05/2020
7800106X311/12/2020
8
Test
Cell Formulas
RangeFormula
F2F2=INDEX(B2:B20,MATCH(1,(A2:A20=E2)*(C2:C20=MAX(IF(A2:A20=E2,IF(C2:C20<TODAY(),C2:C20,0)))),0))
Press CTRL+SHIFT+ENTER to enter array formulas.
 
Upvote 0
Solution
Thats work perfect, thankyou so much fluff, ill try to pick apart the forumula to understand how works now, so i can help others in future.

Much Love <3
 
Upvote 0
You're welcome & thanks for the feedback.
 
Upvote 0

Forum statistics

Threads
1,223,164
Messages
6,170,444
Members
452,326
Latest member
johnshaji

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
Back
Top