VBA: vlookup through multiple columns

Question

this is my first question so I will try my best to format this. I would like to use the vlookup function in more than just one column. It seems impossible to do is in the formula for the large data set, so maybe someone with VBA knowledge can help me.

In my excel file I've two spreadsheets: Sheet1 with one column "A" with 16868 different processes ID's. Sheet2 with columns from "A" to "HX", where "A:HW" are different processes ID's (each column have different numbers of rows) and in "HX" column I have a region for the particular row of processes.

What I am trying to achieve is to do vloop for sheet1 column "A" that will look for each record in table sheet2 and return "HX" when found it.

In example: I'm looking for "A2" cell in sheet1 in table_array sheet2 "A:HX". If found it then return cell in the same row but from column "HX". The trick here is when the looking value is not in the sheet2 column "A", then it should vlookup in column "B", then "C" and so on till "HW". There is an option where the looking value might not be in sheet2 at all and then the formula should return "0". Is it possible to do this in VBA or excel formula?

Answer 1

Firstly, please NEVER use VLOOKUP. Ever.

An INDEX/MATCH combination is always better in every way. It is faster, more flexible and more robust as it does not break if you insert columns. It is also (arguably) easier to use as you do not need to count columns.

Anyway, back to the problem at hand. You can use an INDEX/SUMPRODUCT combination for this:

=INDEX(<range of return values>),SUMPRODUCT(--MAX((<range to search in>=<value to search for>)*ROW(<range to search in>)))-(ROW(<range to search in>)-1))

The SUMPRODUCT returns the last row number where the value is found.

The last bit -(ROW(<range to search in>)-1) is just to make the returned value relative to the searched range (rather than an absolute row number.

The INDEX is then uses this value to select a value from the HX column.