Archives: Lookup and References

MATCH fumction, just a simple search

To do a search in a column or a row, MATCH is the function to look for It is very simple, you set the value to look for and where to look, then of course there is some another option to set how to do the search. So, we are looking for 87 in column • Read More »

Links between two workbooks – dynamically using functions

I don’t believe I don’t have this here yet. This is one of my oldest tricks When we have to do links between two workbooks (One cell in Workbook1 to have value updated from a cell in Workbook2), we usually just do links like this… Go to workbook1, select a cell, type in ‘=’ then go to • Read More »


I have been asked for a while now to just put a simple VLookup function, here it is, I know this might be stupid to post it here, but we need to fill requests from all types of visitors. Vlookup is the most famous function in Excel, some people actually refer to Excel as VLookup. • Read More »

Extract current (Active) workbook name

I need that more often than I thought It is basically extract the full workbook name that we are in, with no folders, and with no extension =MID(CELL(“filename”,A1),SEARCH(“[“,CELL(“filename”,A1))+1,SEARCH(“]”,CELL(“filename”,A1))-SEARCH(“[“,CELL(“filename”,A1))-6) So, here it is….   And if we want to go 1 step further, I often have the version of the tool as part of the filename, • Read More »

Why I ran from =INDIRECT() function

I think if there is a single function that would be a huge threat to VBA and macros from user perspective, it would be INDIRECT() Yes, it wasn’t long enough before I got introduced to OFFSET() back in 1998, and was using it a lot and in my classes, yet once I found INDIRECT, things • Read More »