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

Control Word Wrap
Press Alt+Enter to move to a new row in a cell. Lets you control where the words wrap.

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
62,252
Office Version
  1. 365
Platform
  1. Windows
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’)
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
62,252
Office Version
  1. 365
Platform
  1. Windows
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.
 
Solution

Revier

New Member
Joined
Nov 9, 2018
Messages
10
Office Version
  1. 2016
Platform
  1. Windows
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
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
62,252
Office Version
  1. 365
Platform
  1. Windows
You're welcome & thanks for the feedback.
 

Forum statistics

Threads
1,141,073
Messages
5,704,140
Members
421,328
Latest member
mippy

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