UDF - xlookup

[Scott Huish]

I wrote the following to do a cross reference kind of lookup. The idea behind this is to find a value based on the intersection of two values. So in this example, it would find the value where D13 and 25 intersect. It seems to work, if you find it useful or there is a better way to do this, any comments are appreciated.

The syntax for the function is =xlookup(range,string or cell reference to look for vertically, string or cell reference to look up horizontally)

Public Function xlookup(xrange As Range, vsearch As String, hsearch As String)
Dim a As Range, vrange As Range, hrange As Range, c As Range, d As Range
Dim f As Integer, g As Long, i As Long, j As Integer
Dim FirstAddress As String, SecondAddress As String

Set a = xrange

f = a.Column
g = a.Row
i = Cells(65536, f).End(xlUp).Row
j = Cells(g, 256).End(xlToLeft).Column

Set vrange = Range(Cells(g, f), Cells(i, f))
Set hrange = Range(Cells(g, f), Cells(g, j))

With vrange
    Set c = .Find(vsearch)
    If Not c Is Nothing Then FirstAddress = c.Address
End With

With hrange
    Set d = .Find(hsearch)
    If Not d Is Nothing Then SecondAddress = d.Address
End With

If Not c Is Nothing And Not d Is Nothing Then
    xlookup = Cells(c.Row, d.Column)
Else
    xlookup = "N/A"
End If

End Function

xlookup.xls
	A	B	C	D	E	F	G	H	I	J
1	42
2
3
4
5
6					1	3	9	14	22	25
7				A	1	2	3	4	5	6
8				B	7	8	9	10	11	12
9				C	13	14	15	16	17	18
10				D	19	20	21	22	23	24
11				E	25	26	27	28	29	30
12				F	31	32	33	34	35	36
13				G	37	38	39	40	41	42
14				H	43	44	45	46	47	48
15				I	49	50	51	52	53	54
16				J	55	56	57	58	59	60
17				K	61	62	63	64	65	66
18				L	67	68	69	70	71	72
19				M	73	74	75	76	77	78
Sheet1

 

[fairwinds]

Hi,

I would prefere a normal INDEX( MATCH() MATCH()) formula. Then you do not need the code (getting macro alerts) and you can specify match criteria ( exact match or...)

 

[Tom Urtis]

Curious, and maybe I'm missing something obvious, but is there an advantage to this beyond the native INDEX MATCH MATCH functions?


Edit, I see fairwinds' similar inquiry also.

 

[saurabh726]

im trying to use xlookup in excel sheet,however it does not give me any result..it simply shows me the formula that i wrote in that column only..nothing else...is there is something wrong..??

Saurabh

 

[Scott Huish]

OK, I'm trying to do it that way but obviously something is amiss here, because I'm not getting the right result? Xlookup was an idea I had, syntax is a lot simpler, but maybe it isn't the way to go. What am I doing wrong with the INDEX MATCH MATCH?

xlookup.xls
	A	B	C	D	E	F	G	H	I	J
1	35
2	42
3
4
5
6					1	3	9	14	22	25
7				A	1	2	3	4	5	6
8				B	7	8	9	10	11	12
9				C	13	14	15	16	17	18
10				D	19	20	21	22	23	24
11				E	25	26	27	28	29	30
12				F	31	32	33	34	35	36
13				G	37	38	39	40	41	42
14				H	43	44	45	46	47	48
15				I	49	50	51	52	53	54
16				J	55	56	57	58	59	60
17				K	61	62	63	64	65	66
18				L	67	68	69	70	71	72
19				M	73	74	75	76	77	78
Sheet1

 

[Scott Huish]

Never mind, figured it out:
=INDEX(E7:J19,MATCH("G",D7:D19,0),MATCH(25,E6:J6,0))