vlookup to index/match or match/offset

I have a little project I was interested in trying.

I want to have a macro that converts Vlookups to match/offset or index/match.

I wonder if anyone here has done it before or can give me a head start on the best way to do it and if I'm going to run into any situations where they cannot be converted.

So basically you would enter a vlookup formula in one cell (or column) and it would take the formula - read its parts and use them to reconstruct it as one of the other two, and put that formula in a cell next to the origional.

Attach a sample workbook. Make sure there is just enough data to make it clear what is needed. Include a BEFORE sheet and an AFTER sheet in the workbook if needed to show the process you're trying to complete or automate. Make sure your desired results are demonstrated, mock them up manually if needed. Remember to desensitize the data.

Click on GO ADVANCED and use the paperclip icon to open the upload window.

View Pic

File attached

Enter the value in A2 and press a button or key combo and get the result in B2.

Data is just a random sample in practice it could be any simple usage of vlookup (i.e. no dynamically calculated ranges etc). But I tried to show the vlookups could be done in all sorts of ways.

If it loops through each row that is a bonus. If it handles cross sheet or cross workbook lookups that is also a bonus but not essential.


I'm not picky on exactly how the offset/match is written as long as it gives the same result.

vlookup2.xlsm.

sorry my comment in the attachment should say
"enter data in A2-A4 get result in B2-B4"
not
"enter data in A2-A4 get result in B2"
(I should be more careful)

For some background - in essence this would be an educational tool - showing people with very basic skills (and limited willingness to give you their attention), but familiarity with vlookup - how to do this methodology with their own examples (without anyone needing to watch them or explain anything).

They just throw the formula they are currently using in and it converts it (like I might be able to do if I was there standing next to them).

I might extend the strategy to other formula too once I see how someone would do it. I could try to do it myself but it I think I'd be using very poor methodology.

A few questions/observations...

1. why? why not just show them how a vlookup works, and how index/match works? And more importantly, when (and why) to use which?
2. You do know that if you enter 33 in D2, the formulas in A2:B3 return an error?

Originally Posted by FDibbins

A few questions/observations...

1. why? why not just show them how a vlookup works, and how index/match works? And more importantly, when (and why) to use which?
2. You do know that if you enter 33 in D2, the formulas in A2:B3 return an error?

1. Yes I suggested doing that (releasing training documents), but that and other simple options were frustrated by various factors.
2. Yes because you'd have to change the vlookup range to lookup 33 on the table otherwise it looks for 33 in the first column of the vlookup table.
I didn't mean to make it confusing - just to give some varied test cases.

I can write up some logic. If I want to go between these two below (an offset slightly modified to make it more logical) I could just go

=VLOOKUP($D$2,$F$2:$H$6,3,FALSE)
=OFFSET($F$2,MATCH($D$2,$F$2:$F$6,FALSE)-1,3-1)

now pull those formula into vba -
add on beginning after the = "offset(y" where y = first cell in range
replace vlookup with match
second letter in range is changed to match first letter ($H$6 -> $F$6)
add -1 before vlookup offset
add -1 after vlookup offset

I think that has it covered unless I missed something . Might be even simpler if I switch to RC reference style for the editing...

Now I imagine I'm using split on multiple delimiters (:and,) and replace in vba then reconstruct from the array.

So I get the array below and then reconstruct it
=VLOOKUP($D$2
$F$2
$H$6
3
FALSE)

But I haven't used that before... I'll start on some research for now.

this seems to work OK (I will test it a bit more).
First section is chip persons SplitMultiDelims code to make life easier.

Please Login or Register  to view this content.

and for what its worth offsetmatch

this version doesn't handle fixed ranges ($) - the first one makes all ranges fixed.

Please Login or Register  to view this content.