Vlookup/cell linking
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!
Comments
-
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))
-
Thank you!
I think I'm confused about how you pinpoint the column that you want the value to come from. In vlookup, you type the column number. How/where do you do this with index match?
=vlookup(search_value, lookup_table, column_num, [match_type])
search value- Today
lookup table- Range
column number- the number of columns from the initial column (date) where you find the information that you're looking for.
match_type- false bc I want only answers that match the date exactly.
So, with index match, I would use:
=INDEX({range}, MATCH(["what you want to match", {range}, 0))
Range- lookup table
"what you want to match"- insert a reference to a column with today's date
{range}- same range as lookup table?
-
It isn't a table exactly. There are 2 column references. The column reference in the index formula is your return. The column in the match is where you look up the columns. Index match is faster because there isn't a table and all of the extra data. In fact if you move the columns closer your vlookup formula will work much faster.
Ex:
index([Column2]:[Column2],match("value",[Column3]:[Column3],0))
Help Article Resources
Categories
Check out the Formula Handbook template!
