Variable column for VLookup possible?

Good day to all,

I am pretty sure it's possible, but somehow I can't get it working. Should probably be something with '(column())' or something like that.

What am I talking about? Well, I am trying to find a formula with VLookup that, every time I copy the formula to the right the search will be in another column, too (also one to the right).

This is the basic formula I got: IFERROR((VLOOKUP($C5;Sheet1!$C$5:H$5000;6)*H$4);"-")
I underlined the '6', because I know that's the one number that should be changing. When I copy the formula one column right, the result would (should) be: IFERROR((VLOOKUP($C5;Sheet1!$C$5:I$5000;7)*H$4);"-")
It's just that I'm talking about over 500 columns, may be even 1,000 at the moment, and I have no wish to re-enter the formula every time. So, is there a way to auto-change this (for example by using 'row()' of 'column()')?
See attachment.

Many thanks in advance!

I think this is what you mean. In F5 copied across and down:

=INDEX(Sheet1!$F$5:$H$30,MATCH($A5&$B5,Sheet1!$A$5:$A$30&Sheet1!$B$5:$B$30,0),MATCH(F$2,Sheet1!$F$2:$H$2,0))

You will need to change commas to semi-colons for your Dutch locale.

Add your IFERROR function as required.

If you unhide column C, you'll see that I put in there a small formula (= A4&", "&B5). Just needed that in all my tabs for other VLookup (I'm talking about a 2,000MB file here).

Sadly, this gives me a #VALUE...

OK - did you really think I was going to hunt for hidden columns???

I'll have another look, but it would help ENORMOUSLY if you would mock up the results you want instead of showing us something that isn't what you want ...

This is what you want:

=INDEX(Sheet1!$F$5:$H$30,MATCH($C5,Sheet1!$C$5:$C$30,0),MATCH(F$2,Sheet1!$F$2:$H$2,0))

I think ...

The result I want is in Sheet2 of my original added file... So, I am sorry, but I don't get why you give me that remark - no offence meant. I have no idea how to be more clear in my question, though. I really thought I wrote what I do want, and that is the result shown in Sheet2 of the original uploaded file.

"MATCH" is not going to work. I do not wish to copy the contents. As you can see in the original file, I am multiplying the result (if possible, hence then "IFERROR") with the length of the circuit (row 4 in Sheet2). Sadly, that ain't working, apparently, with "MATCH".

No offence taken.

I am sorry - I don't get what you are looking for.

Last try. In F5 copied across and down:

=IFERROR(INDEX(Sheet1!$F$5:$H$30,MATCH($C5,Sheet1!$C$5:$C$30,0),MATCH(F$2,Sheet1!$F$2:$H$2,0))*F$4,"-")

Tnx, that seems to be working. Thank you.

Great!

If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED.

Also, if you have not already done so, you may not be aware that you can thank those who have helped you by clicking the small star icon located in the lower left corner of the post in which the help was given. By doing so you can add to the reputation(s) of all those who offered help.

Administrative Note:

Members will tailor the solutions they offer to the version of Office (Excel) that you have. Please check that your forum profile is up-to-date in this respect. If you aren't sure, in Excel go to File | Account and report what it says below the MS logo at the top of that page. If your version is for Mac, please also state this. Thanks.