Tag Archives: VLOOKUP

VLOOKUP and HLOOKUP

VLOOKUP is arguably the most useful power-function in Excel. Time and time again, I call upon its beauty to bring back a value from a simple array of data. But we shouldn't forget about its lesser-used sibling, HLOOKUP. First, VLOOKUP. … Continue reading →

Number to text converter: most useless spreadsheet ever

I can’t remember why, but I set myself a personal challenge some time back to create a spreadsheet that could convert numbers to text. Here it is. Type in a number between 1 and 999,999 in the red cell and … Continue reading →

Variable vlookup offsets

Challenge: I want to future-proof the column number returned in my VLOOKUP formula. VLOOKUP is a hugely useful formula in Excel. Apart from the basic mathematical operators and the IF statement, there’s nothing that touches it in terms of how … Continue reading →

Hiding errors

Challenge: I want to hide error values. Error values are pretty ugly. #N/A, #VALUE, #REF etc. When the eye sees them, it triggers an ever so slight wince. They occur when something's awry: when lookups don't yield any results, when … Continue reading →

Breaking rank—differentiating between values that are equal

Challenge: On a dashboard report, show the top five values from a table of data showing the number of respondents. And against these values, provide other data from the same records. At first glance, the above challenge seems rather simple. … Continue reading →

Killer Sudoku Pro: spreadsheet helper

Challenge: There is a variant of Sudoku called Killer Sudoku in which areas of the grid, either square, rectangular or some other irregular shape, are caged off. A small number in the corner indicates the value to which the numbers … Continue reading →