Excel function: nXLookup

I really like VLOOKUP, it has to be one of the most useful Excel functions that I use all the time. So I just had to ask myself how could I possibly make it more useful to me. With that in mind I have yet to find an instance where I don't use FALSE at the end; I'm sure there must be a good use for TRUE out there somewhere, but my fear that TRUE is "non-stable" has made me wary of using it altogeather, so if and when I really needed a stable nearest neighbor function I'll probably develop one at that time. Instead I went with a combination of both VLOOKUP and HLOOKUP that will do(I think) most of the odds'n'ends lookup requests I've seen posted on this forum from time to time. (that and I got tired of not being able to do negative columns in VLOOKUP)

Please Login or Register  to view this content.

	A	B	C
1	X	A	1
2	y	B	2
3	=TEXT("Z","General")	A	3

H/VLOOKUP/MATCH Equivelents:

Standard VLOOKUP
=VLOOKUP("a",B1:C3,2,FALSE)
=nXLookup("a",B1:B3,0,1)

Contains VLOOKUP
=VLOOKUP("*A*",B1:C3,2,FALSE)
=nXLookup("A",B1:B3,0,1,,,,FALSE)

Standard HLOOKUP
=HLOOKUP("a",A1:C3,2,FALSE)
=nXLookup("a",A1:C1,1,0,,,,,FALSE)

Standard MATCH
=MATCH("b",B1:B3,0)
=ROW(nXLookup("b",B1:B3))

Non-Equivelents:

VLOOKUP cannot go negative, and returns a value not a reference
=VLOOKUP("a",B1:C3,-1,FALSE)
=nXLookup("a",B1:B3,0,-1)
=OFFSET(nXLookup("a",B1:B3),0,-1)

VLOOKUP doesn't have a case sensitive option
=VLOOKUP("Q",A1:C3,3,FALSE)
=nXLookup("Q",A1:A3,0,2,,,,,,,TRUE)

VLOOKUP doesn't have an option to look in formulas, nor comments
=VLOOKUP("T",A1:C3,3,FALSE)
=nXLookup("T",A1:A3,0,2,,,2,FALSE)
=nXLookup("T",A1:A3,0,2,,,3,FALSE)

VLOOKUP cannot find the N^th Match, nor 4th out of 2 match
=VLOOKUP("A",B1:C3,2,FALSE)
=nXLookup("A",B1:B3,0,1,2)
=nXLookup("A",B1:B3,0,1,4,TRUE)

VLOOKUP doesn't have an option to start from the bottom up
=VLOOKUP("A",B1:C3,2,FALSE)
=nXLookup("A",B1:B3,0,1,,,,,,FALSE)

nXLookup cannot find "First Pigeon Hole?"
=VLOOKUP("Y",A1:C3,3,TRUE)

nXLookup can also do a "random table access lookup?"
=nXLookup("A",A1:C3,-1,-1,2)
=nXLookup("A",A1:C3,1,1,2)

Now if only I could get the parameter names to show when typing out the function into a cell I'd be golden.

Hi Gregor y,
As for your final note: Do you mean something like: http://stackoverflow.com/questions/4...fined-function

Welcome to 2019!

https://techcommunity.microsoft.com/...UP/ba-p/811376

still missing some of its functionality, but a good step in the right direction.

thank you for sharing