VLOOKUP to the left – using OFFSET and MATCH

by Michael Pennington

It is really just a tip that I picked up somewhere in the last few years and I use it all the time to do Vlookups that can go to the left instead of just the right. Simply an offset and match combined for the purposes of replicating a left looking vlookup. I used a nice add in to create sample data and linked to the add in within my workbook. I find myself using this instead of always reworking data sets that I receive. Feel free to use the workbook however you want, if you want to present it in another fashion or know a better way, I look forward to your input.

Download Links:

reverse-vlookup.xls

via 9 Excel Tips & Downloads Submitted by Our Readers [Reader Awesomeness Week] | Chandoo.org – Learn Microsoft Excel Online.

Do you know that you can use excel find – replace dialog to change cell formats?

Often we prepare a report or project plan and our boss or someone else in the team feels that all the red color cells need to change to blue or something similar, worry not, you can use find-replace (ctrl+h) to do this for you.

Press Ctrl+h

click on “Format…” button on “find what:” area and specify the source format

click on “Format…” button on “replace with:” area and specify the target format

Select “replace all” or “replace” depending on your need

Smile, you have just automated a mechanical and boring task

via Change cell formats with find-replace – simple excel trick that can save time | Chandoo.org – Learn Microsoft Excel Online.

SPACEBAR In a dialog box, performs the action for the selected button, or selects or clears a check box.