Can somebody explain how this formula works?

Hi, I have been given a worksheet with a formula I don't quite understand, and i'd like to know how it works so I can continue to work with the sheet. It is a vlookup formula with an IF statement inside, and the IF statement has a logical test that I do not understand.. It is as follows..

=VLOOKUP(G56,IF({1,0},G6:G47,$C$6:$C$47),2,0)

What is the logical test doing? and how does this let the Vlookup formula achieve its goal? Finally, what other used could this have?

Many thanks -

O

It's allowing you to do a reverse lookup, which is not natively possible with VLOOKUP, as it works from left to right. This is effectively mimicking an INDEX MATCH lookup. Use the Evaluate Formula feature to step through the formula and see how it calculates.

The {1,0} is telling Excel that the G range is column 1 of the lookup array and the C range is column 2.

As we have no idea how it is being used in your scenario, we cannot tell you how ELSE it may be used, but suffice it to say that there is a whole host of uses for VLOOKUP and INDEX MATCH fornulae.

Please use the Evaluate Formula feature to step through and see the formula in action - that will make everything clear for you. It's on the Formulas ribbon.

You can achieve the same with the CHOOSE function:

=VLOOKUP(G56,CHOOSE({1,2},$G$6:$G$47,$C$6:$C$47),2,0)

You could simply replace it with
=INDEX($C$6:$C$47,MATCH(G56,G6:G47,0))

Yes, indeed, and I alluded to this (but failed to illustrate it) in my second sentence in post #2. I wonder if there is any significant difference in overhead between the three if used on a large dataset?

I suppose that would depend on your definition of significant. Since the CHOOSE version is creating arrays from the ranges, and since INDEX/MATCH is typically faster than VLOOKUP anyway, in my experience, I'd definitely use INDEX/MATCH- especially as it's more intelligible.

Yes, I'd do that, too. Glad to have helped!

I still don't see the logical reasoning behind the use of the IF function ...

Ditto. To be honest, I hadn't seen it done with IF before, only CHOOSE.

That's why I said "in my experience". I've seen articles and tests that have argued each is faster than the other, so I tend to work on the principle that it will vary based on your data setup- I can't honestly see how VLOOKUP referencing a 20 column range could work better than INDEX & MATCH referencing only two of those columns.

I suppose it's not really an issue unless it becomes an issue! We all know that massive datasets can start to crawl, especially if OFFSET and/or array formulae (with INDEX MATCH) are used. As you say, it depends entirely on the dataset and use of the functions in question.