Archives: Lookup and References



OFFSET+MATCH

One of the power of combining functions in one cell is using the best of these functions An example is here, OFFSET + MATCH will allow you to do something similar to VLOOKUP. Why do we want to do that? VLOOKUP will search in the left-most column in a table to get a value from • Read More »


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 »


VLookup

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 »