PDA

View Full Version : Simple UDF....or so I thought.



jproffer
12-29-2009, 11:43 AM
Function DualLookUp(ColFind As String, RowFind As String, TableArray As Range)


Set clmn = Range(TableArray).Find(ColFind, LookIn:=xlValues)
Set rw = Range(TableArray).Find(RowFind, LookIn:=xlValues)

DualLookUp = Cells(rw.Row, clmn.Column).Value
End Function

My mind is about on meltdown I think, lol.

Why is this syntax wrong?

Basically, I want to find a column label and a row label within a table, then find the intersecting value.



..............Shoes..........Hat..........Shirt..........Pants
Blue A B C D
Red B C D A
Green C D A B
White D A B C


If you want to see who's wearing the red hat, it should return person C...but alas.

Any thoughts?

Thanks in advance.

jproffer
12-29-2009, 12:28 PM
Function DualLookUp(ColFind As String, RowFind As String, TableArray As Range)

tbl = TableArray.Address

Set clmn = Range(tbl).Find(ColFind, LookIn:=xlValues)
Set rw = Range(tbl).Find(RowFind, LookIn:=xlValues)

rwadd = rw.Row
coladd = clmn.Column

DualLookUp = Cells(rwadd, coladd).Value
End Function

Just figured it out, but I still want to thank everyone for all the past help.

Since I'm this close I might as well ask this now.

Re: the same function shown...

Is there a way to make the .Find lines look ONLY in the left-most column (or the upper-most row...whichever applies)?

Not a really big deal, just curious.

Thanks again to everyone for all the help...past and present. :)

stanleydgrom
12-29-2009, 01:03 PM
jproffer,

See the attached workbook "Index Match Match - jproffer - SDG11.xls" with the formula in cell I2 (coped down):
=INDEX($A$1:$E$5,MATCH($G2,$A$1:$A$5,0),MATCH($H2,$A$1:$E$1,0))


Have a great day,
Stan

jproffer
12-29-2009, 01:46 PM
I knew there was a formula(r ??)....formular...I think that's a word, lol.

Anyhow, I knew there was a way to do it with formulas, but I thought maybe it would be a useful thing to have....and I was bored...but now I know both ways.

Thank you

Aflatoon
12-30-2009, 06:52 AM
Try:


Function DualLookUp(ColFind As String, RowFind As String, TableArray As Range)

With TableArray
Set clmn = .Rows(1).Find(ColFind, LookIn:=xlValues)
Set rw = .Find(RowFind, LookIn:=xlValues)

rwadd = rw.Row - .Row + 1
coladd = clmn.Column - .Column + 1

DualLookUp = .Cells(rwadd, coladd).Value
End With
End Function

It is not a good idea to have an unqualified reference to Cells in a UDF.