VB code to replace HLOOKUP

[MarcusEd]

Hello

Being new and unfamiliar with the possibilities of VB in Excel, I’m hoping someone could help me with a solution to some code I’ve been trying to write.

Basically there are three tables:

First table is an input table that will extend down for several hundred rows eventually but I’ve limited to just a couple for the example,
A reference table
An output table – this used to be an HLOOKUP table but this has become too cumbersome and I’ve run out of arguments in the formula, hence resorting to code instead.

Basically starting with each row of data in the input table, need to reference the value of each successive column to the corresponding date row in the reference table. Then put the resultant date in the output table.

For example refer Input table cell E2 (7,000), refers to the reference table will return this value occurs in Aug 19 which is then returned in the output table.

I need the code to loop through multiple rows of the input table and returning the corresponding date value in the output table.

Many thanks.

Input table 1st 2nd 3rd 4th 5th 6th 7th 8th 9th 10th
Row1 1000 3000 5000 7000 9000 11000 13000 15000 17000 19000
Row2 5000 10000 15000 20000 25000 30000 35000 40000 45000 50000


Reference table Jan-19 Feb-19 Mar-19 Apr-19 May-19 Jun-19 Jul-19 Aug-19 Sep-19 Oct-19 Nov-19 Dec-19 Jan-20 Feb-20 Mar-20 Apr-20
Row 1 0 1000 2000 3000 4000 5000 6000 7000 8000 9000 10000 11000 12000 13000 14000 15000

Output table 1st 2nd 3rd 4th 5th 6th 7th 8th 9th 10th
Row1 Feb-19 Apr-19 Jun-19 Aug-19 Oct-19 Dec-19 Feb-20 Apr-20 Jun-20 Aug-20
Row2 Jun-19 Nov-19 Apr-20 Sep-20

 

[Fluff]

With the limited data you have shown this can be done with a simple formula

Excel 2013/2016
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q
1	Input table	1st	2nd	3rd	4th	5th	6th	7th	8th	9th	10th
2	Row1	1000	3000	5000	7000	9000	11000	13000	15000	17000	19000
3	Row2	5000	10000	15000	20000	25000	30000	35000	40000	45000	50000
4
5
6	Reference table	Jan-19	Feb-19	Mar-19	Apr-19	May-19	Jun-19	Jul-19	Aug-19	Sep-19	Oct-19	Nov-19	Dec-19	Jan-20	Feb-20	Mar-20	Apr-20
7	Row 1	0	1000	2000	3000	4000	5000	6000	7000	8000	9000	10000	11000	12000	13000	14000	15000
8
9	Output Table	1st	2nd	3rd	4th	5th	6th	7th	8th	9th	10th
10	Row1	Feb-19	Apr-19	Jun-19	Aug-19	Oct-19	Dec-19	Feb-20	Apr-20
11	Row2	Jun-19	Nov-19	Apr-20
Today

Cell Formulas
Range	Formula
B10	=IFERROR(INDEX($B$6:$Q$6,MATCH(B2,$B$7:$Q$7,0)),"")

Copy formula down & across