VLOOKUP to Return whole horizontal row values in vba

[jeyachandran]

Hi sir,
I need an UDF macro function for VLOOKUP to Return whole horizontal row values. ie when Select Drop Down list in sheet1 (Noun-Modifier), then
Sheet 2 Catted Value automatically populated as per example attached sample file. Could you resolve my query.

 

[AlanSidman]

Used a combination of Power Query and Power Pivot to achieve your expected results as shown in the attached.
Unpivot your data in Power Query and then load the data into the data model to allow a measure to be built to show your data in a pivot table with a Pipe delimiter.

 

[jeyachandran]

Thanks for your reply sir.. Am currently using excel 2007..so i dont have power query and power pivot.that why am asking you to solve this query in vba function. Could you help me.

 

[AlanSidman]

Sorry, but I cannot help with those requirements.

 

[BobBridges]

Jeya, you can write a VBA program that can find a row and do something with it, perhaps copy all its values somewhere. But it wouldn't be a UDF; a user-defined function cannot return a whole row, only a single value.

 

[BobBridges]

One correction: When I say you can't do it, I mean in a worksheet function. If you enter a worksheet function in a cell (eg "=SUM(A5:A23)"), it can return only a single value whether it's a built-in function or a VBA program you wrote yourself.

But if you're talking about a function called by another VBA program, that function can indeed return an Excel object, including a Row object. Which did you mean?

 

[jeyachandran]

Thanks for reply sir..
I understood what you have said. Can Sub procedure (Sub-Routine) macro help to solve this task.?

 

[BobBridges]

I'm not sure. I suppose it depends on this: What do you want to do with the row once your routine has identified it? There are usually ways to accomplish what you want, even if you can't do it the way you were asking about.

 

[p45cal]

In the attached, horrible formula in cell D2 and below of sheet Sheet1, copied across every 3rd column:

=SUBSTITUTE(TRIM(MID(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(VLOOKUP($C2,Sheet2!$A$2:$B$16,2,0)," ","¬"),"|"," ")," ",REPT(" ",1000)),(VALUE(SUBSTITUTE(D$1,"Attribute ",""))-1)*1000+1,1000)),"¬"," ")

This formula returns #N/A when it doesn't find what's being looked up so you could wrap the entire formula in an IFERROR function.

 

[p45cal]

The attached has a UDF myAttribute, used in rows 10 to 15 of Sheet1.
Use thus:

 

[jeyachandran]

Thanks for reply sir..this formula works fine.But some time Excel hangs and Not responding frequently. Is there any changes Needed?

 

[p45cal]

Not as far as I know.

 

[jeyachandran]

Thanks sir.. i will check it.