Converting Hlookup to Index Match with variable column index number

**SimonLock** · 01-09-2015, 08:11 AM

Hello,

I currently have a hlookup formula as follows in F4:

=HLOOKUP(B4&"3",$1:$500,2+M4,FALSE)

This formula is copied down over hundreds of rows, so the column index number in M4 becomes M5 then M6 etc, all of which have different values.

Now, as this is a large spreadsheet (60mb) I have been told that using Index Match is more efficient than Hlookup. However, I can't figure out how to do it as usually you select the row you want for the match array, and I want it to change on each row based on the value in M.

Firsty, is it true that Index Match would work better here, and if so can anyone tell me how to do it?

Thanks in advance.

**udprocks** · 01-09-2015, 08:15 AM

Hi,

Please upload a sample file if possible.
And yes "Index match" is better than "vlookup & Hlookups"

**Glenn Kennedy** · 01-09-2015, 08:16 AM

OK, it's a big sheet, but can you post just a few rows so that we can see what you're trying to achieve?

**SimonLock** · 01-09-2015, 08:23 AM

Example.xlsx

The hlookups are in column F, the column index numbers arein column M.

Edit: Calculations are set to manual

**SimonLock** · 01-09-2015, 08:24 AM

Oh, and calculations are set to manual, sorry.

**Glenn Kennedy** · 01-09-2015, 09:08 AM

Hi. is this what you wanted? You'll see that I've changed the +2 to +1, 'cos the index match setup runs on the basis of the satrting point of the array (row2) rather than row 1