Vlookup/cell linking

sstrasmore

Hi All,

I've been having a lot of trouble with a vlookup formula that I have linking a sheet with ALOT of columns--just under 200. There seems to be a  limit to the number of columns to 'look at' (which is 85.) This is the last formula that I have which works. 

=VLOOKUP(TODAY(), {1. DOR (Pull-out) Range 1}, 85, false)

I tried to create a secondary date column and then do the vlookup using that column but seem to break the old formula when creating a new range. 

It was suggested to me that I may be better off with an index match but I have not yet gotten through to that. 

I would really appreciate any insights anyone can offer! 

L_123

Vlookup is not a very efficient formula, I never use it. The main reason being is how many forced cells it calculates across. Let me give you an example

We have a single sheet with 10 columns. Their names are:

C1 C2 C3 C4 C5 C6 C7 C8 C9 C10

Lets say we want to return a value in C10 based on a value in C1, and we want to check the entire column. If we use a vlookup, the program is calculating across each column between C1 and C10. So the program has to go through C1 C2 C3 C4 C5 C6 C7 C8 C9 AND C10. We can achieve the same results while only looking at C1 and C10 by using the index(match())

Index match sounds complicated, and looks intimidating, but in fact is a very simple concept. Let's take a look at each part

Index() is a very straightforward formula. It returns the Xth value in a range.

=Index(C1:C10,1) would return what value is in C1 or the first value in the selected range.

Match() finds a value in a range, and returns that value's location.

=match(1,C1:C10,0)

would return a number correlating to the first 1 in C1:C10

We can put these together to return a value.

Index({range where you want to return the value},match("What you want to match",{range where you want to lookup the value},0))