Seems a bit dangerous to mix methods that do the same thing. That's just me, though - I never bothered learning how to use V/HLOOKUP. (It's been quite a while since I last used Excel.)

For large spreadsheets, I usually disable automatic calculation.

V/HLOOKUP as i recall demands that the data is in sorted order. You can specify the data in ascending or descending order. Being sorted means it's probably very fast, whereas just finding/matching might do a full manual search rather than a binary search.

As for exact matching, there's an option somewhere that if there isn't an exact match that you can have it round, ceiling or floor to the proper answer. So say... You have a list of primes and you enter 10, it would go to the next prime in the list, which then returns 11. Doing an exact match would probably return an error or empty result. But that's a weak example, it would probably be stuff like dates and model numbers and stuff...

Usually i just look at the calling convention and it tells you everything you need to know about it. I rarely memorize Excel commands (i don't use it enough in my off time)

There are a lot of ways to do things in Excel. I once took over and managed a WoW Rogue DPS spreadsheet. Added Macro functionality to download item data from the Wow database as well as auto optimize attack rotations and rank gear based on one's specific race, items, talent selections, etc. It was a quite intense experience. Used a lot of my math background in simulating the game mechanics accurately. Every mechanics change and new items required updates and God forbid, I tried to keep it as backward compatible as possible as well as maintaining a no macro Mac Excel and separate Open Office version. The Workbook probably had 15 or so interconnected sheets at the end. Dropped the project during an expansion as it was going to be too much work to redesign the whole thing with the new game changes plus I was burned out on the game.

The moral here is you don't have to understand advanced Excel techniques, but when you ask them about it, it shouldn't be that hard to tell whether they know their business as they explain what they have done in the past and how they've done it...you might lose out on a talented person by asking too specific of questions about things that can be done another way.

Ok I have written my questions about Excel. I agree that someone on the panel needs to know more about it but I was thrown in at the last min.


BTW I +1 everyone that helped

VLOOKUP only needs data sorted for approximate matching. If you use exact matching then it doesn't matter what order the text or numbers are in. However, it will stop at the first instance it finds.

http://office.microsoft.com/en-gb/excel-help/vlookup-HP005209335.aspx


One advantage of INDEX and MATCH is that you specify where you are looking for the match, whereas VLOOKUP works on the first column of the data range so you only specify the corresponding column to return.


But yes, to the op, as someone has already said pivot tables and VLOOKUP are a must. It is also worth looking at conditional formatting and using tables and defined named ranges.