Error checking lookup formulas

Excel

New member
Joined
Sep 17, 2013
Messages
2
Reaction score
0
Points
0
Although I'm quite experienced with excel I often get into a mess with lookup formulas such as match() and vlookup() is there a good tool or technique for checking these errors? It's just really hard work trying to figure out which cells are being looked up and it's a really common source of errors in my sheets. How do other people get over this issue?
 
It is difficult to give advice without specific examples... Pay close attention to the syntax of the functions... For example:
VLOOKUP(lookup_value, table_array, col_index_number, [range_lookup])

lookup_value is always the value it is trying to find. It must be in the first column of the table_array.
table_array is the set of data you want to look through.
col_index_number is the number of the column in the table_array you want to grab the result from. If you say 1, it will return the lookup_value if it finds it. 2 will be the cell directly to the right of the lookup_value.
[range_lookup] just asks whether the list is sorted. If it is, say TRUE and the VLOOKUP will be slightly faster for long lists. If you are unsure or don't want to worry about it, say FALSE.

I also recommend wrapping all your VLOOKUP and MATCH formulas in IFERROR statements. IFERROR(value, value_if_error) is the syntax.
value is where you put the VLOOKUP function.
value_if_error is where you can put "No Match" or something similar, so that you won't get #VALUE errors in your spreadsheet.

Pay attention to the color-coding that Excel automatically does to tell where you are inside the formulas. It'll help you get out of a jam.

Good luck!
 
Yeah its more of a problem that keeps occurring in different situations. I know the colour coding shows the range thats referenced but as far as I'm aware it wont show exactly which cell is being looked up, I guess thats what I'm after. If its looking up within a large range it's quite hard to check if its looking at the right cell.
 
MATCH will give you the row of the cell that matches. VLOOKUP will give you the cell's value. If you are uncertain about where a VLOOKUP is finding something, swap it out with a MATCH to see what it comes up with. They follow the same matching rules.
 
Back
Top