Translate Index Match formula to VBA

[Mikeymike_W]

Hi All,

I have a formula which i want to remove from my sheet and place in a userform so it is only active when a command button is pressed.

The formula is:

=IFERROR(IF((INDEX(FIPCDNumber1,MATCH(PID,PIDFIP2,0)))=0,"",(INDEX(FIPCDNumber1,MATCH(PID,PIDFIP2,0)))),"")

Big thanks for any help you can provide

 

[Jeffrey Mahoney]

Mike,

Are you asking for the macro to add that formula to a cell on command or do you just need the result? And if just the result, do you want the result in a cell or just a message box?

Jeff

 

[Mikeymike_W]

Hi Jeff,

I just need the result when the command button is clicked and then for that result to appear in a text box which will also be on the same user form.

Thanks for your help,

Mike

 

[Jeffrey Mahoney]

What sheet are these named ranges stored: FIPCDNumber1, PID, PIDFIP2?

 

[Jeffrey Mahoney]

Ok, help me out here. I'm evaluating the formula on my end. I created named ranges like you. This formula seems to be missing the column reference:
=INDEX(FIPCDNumber1,MATCH(PID,PIDFIP2,0),[column])
Otherwise it returns an error. Since PID is the match value you are searching, you need a column number for it to return a value in your table.

 

[Mikeymike_W]

Hi Jeff,

=IFERROR(IF((INDEX(FIPCDNumber1,MATCH(PID,PIDFIP2,0)))=0,"",(INDEX(FIPCDNumber1,MATCH(PID,PIDFIP2,0)))),"")

PID=cell within the form the contents of which I want to find in another workbook

PIDFIP2='FIP.xlsm'!DATATable[PID] This is were i want the formula to refernce and match against "PID"

FIPCDNumber1='FIP.xlsm'!DATATable[CD Number] once matched This is the value i want to be generated and populated in my user form

Hope this is what you needed,

Thanks for your help,

 

[Jeffrey Mahoney]

Still churning...

You currently have a formula in the active workbook that is looking for a value in a table on another workbook you call FIP.xlsm on a sheet called DATATABLE in a named ranged called PIDFIP2?

The active workbook name is? (just for reference)

The Form will be located in the Active workbook or the FIP workbook?

I need you to do me a favor because I want this to work for you. Put this formula in a cell somewhere on your active workbook and tell what result you get. Do you ever get a value you are expecting?
=INDEX(FIPCDNumber1,MATCH(PID,PIDFIP2,0))

Jeff

 

[Mikeymike_W]

Hi Jeff,

The formula you provided definitley works and gives me the value i want when entered into a cell in the workbook.

the active workbook name is CRF.

The Userform will be located in the Active workbook.

Really appreciate your help,

Mike

 

[Jeffrey Mahoney]

The formula you provided definitely works and gives me the value i want when entered into a cell in the workbook.

Ok, I'm confused. I've created two workbooks (FIP and CRF). I created named ranges in CRF that reference the data on FIP called FIPCDNumber1 and PIDFIP2. My formula gives me an error. So can you provide a sample of the lookup table FIPCDNumber1 and tell me what part of that is PIDFIP2.

Sorry for being a little dense on this.

 

[Jeffrey Mahoney]

If I was there looking at what you're looking at, we'd be done by now