Creating a VBA function to combine INDEX and MATCH

**cookie365** · 06-17-2011, 11:29 AM

I hardly ever use VLOOKUP anymore in favour of INDEX(MATCH) but I can never get the order of all the arguments right!

So I want to create a VBA function that combines the two of them - a kind of SuperVLOOKUP that lets you search leftwards and/or start from beyond the first column, with the following arguments:

Source data range
Index
Column within source data range containing index value
Column within source data range containing return value

But I haven't got the foggiest idea how to start - in particular how to pass arguments that could be values, or ranges, or whatever, and values that could be in any format.

Can anyone suggest a starting point - or even better, share a function they've already written to do the job?

Thank you

**jomili** · 07-26-2011, 11:33 AM

I'm bumping this because I, too, would benefit from this. Anybody want to tackle it?

**jomili** · 08-30-2011, 08:50 AM

I'm still interested in a solution. Anyone have insight on this?

**teylyn** · 08-30-2011, 08:55 AM

Your post does not comply with Rule 2 of our Forum RULES. Don't post a question in the thread of another member -- start your own thread. If you feel it's particularly relevant, provide a link to the other thread.

**teylyn** · 08-30-2011, 08:58 AM

Other than that, it's really not all that hard to remember the syntax of Index/Match.

Put up a Post-it on your monitor

Index(Range,match(find row), match(find column))

Using VBA to re-invent the wheel will not be more efficient.

**jomili** · 08-30-2011, 08:58 AM

I'm not cross posting; I'm interested in the answer to the original OPs question, which no one has responded to. No cross purpose here, no other agenda.

**teylyn** · 08-30-2011, 09:09 AM

No, you're not cross posting. You're hi-jacking. That's a different thing. Read the forum rules. It's all explained in there.

**jomili** · 08-30-2011, 09:14 AM

Hi-Jacking means to steer it away from it's original destination, which is not what I did. I'm encouraging it to follow on to it's intended destination. It's like the car stalled in the middle of the street, so I'm calling folks to help push it to it's destination.

**snb** · 08-30-2011, 09:38 AM

Alt-F11/F2/Index/Enter/worksheetfunction

**jomili** · 08-30-2011, 09:44 AM

SNB,
AltF11, and F2 I follow. F2 opens the Object Browser, which I've never used, so don't know where to put in INDEX.

**snb** · 08-30-2011, 09:48 AM

In the only spot you can put in characters in the object browser.

**jomili** · 08-30-2011, 09:51 AM

And then I assume I go to "Worksheet Function" in the "Classes" section?

Thread: Creating a VBA function to combine INDEX and MATCH

Thread Tools

Display

Creating a VBA function to combine INDEX and MATCH

Re: Creating a VBA function to combine INDEX and MATCH

Re: Creating a VBA function to combine INDEX and MATCH

Re: Creating a VBA function to combine INDEX and MATCH

Re: Creating a VBA function to combine INDEX and MATCH

Re: Creating a VBA function to combine INDEX and MATCH

Re: Creating a VBA function to combine INDEX and MATCH

Re: Creating a VBA function to combine INDEX and MATCH

Re: Creating a VBA function to combine INDEX and MATCH

Re: Creating a VBA function to combine INDEX and MATCH

Re: Creating a VBA function to combine INDEX and MATCH

Re: Creating a VBA function to combine INDEX and MATCH

Thread Information

Users Browsing this Thread

Tags for this Thread

Bookmarks

Bookmarks

Posting Permissions