Vba vlookup & match value in combobox and textbox on a userform

hi all,
i'm attempting to run a vlookup and match search in a userform with the values from a text box and a combo box. using a formula works fine in the spreadsheet translating that however to vba not so easy.
the formula is =VLOOKUP(R3,'Rate'!A2:AY157,MATCH(C7,'Rate'!B1:AY1)+1). R3 represents the TextBox ("TextBox1") and C7 represents the ComboBox("ComboBox1")

here's what i have so far (im probably way off)

Please Login or Register  to view this content.

Could you upload your workbook (without any sensitive data)?

here's a edited workbook (with sensitive data removed) forumlas and cells should still apply.
i cant however show the userform too much sensitive data would have to be removed etc.
thanks!

Hi,

I don't think you need to use both the index function and vlookup, as you can get the value column 4 within your lookup range through the index function.

Try this (untested):

Please Login or Register  to view this content.

We can refine it given the input in Sheet1 that I now see, but I hope that will be helpful and possibly in the right direction

whoops almost forgot to add i wanted the result to return to TextBox141(within the userform), any idea where it's returning now, i don't see it?

also i'm not sure why i put column 4 in there that was a mistake. i wanted to return the result (whichever column it may be in) depending on the number in combobox 1 which will be the name of the column its in.

bump, anyone?

It's only a matter of putting the right variables into the code and then define the TextBox141 as myVal. Will the value of textbox1 be the date whereas combobox1 will be the column number?

At the moment, the output (of the code I provided) is shown in the Immediate window (debug.print myVal), which can be displayed in Visual Basic by pressing CTRL+G.

yes (textbox1 is date, and combobox1 is the same as the column number in the first row) i've tired playing around with switching the textbox and combobox values towards the bottom but no dice also the debug is showing no result returned for any combination of alternatives for myVal including application.match(Me.TextBox1.Value,
(same with combobox)

here's the workbook with a userform added.

perfect! thanks a alot!

You're welcome