Forum Discussion
Excel Formula
Hi,
This is a lookup formula that looks into a sheet named Receipt Types within the range A12:B48 for the value in cell A102 of the sheet that formula lives in, so that if formula found the value in the first column of the range A12:B48, it will return the value next to it in the same row.
This how the main part of the formula works.
Main Part
VLOOKUP(A102,'Receipt Types'!$A$12:$B$48,2,FALSE)
The rest of the formula is just to check if the value of cell A102 is not found if so, the formula return 0.
You can learn more about the VLOOKUP formula from this https://support.office.com/en-us/article/vlookup-function-0bbc8083-26fe-4963-8ab8-93a18ad188a1.
With regards to the dollar sign, it used in Excel to lock the range so that it doesn't move when you copy the formula down. Please check out this https://www.ablebits.com/office-addins-blog/2015/11/25/relative-absolute-reference-excel/ to learn more.
The exclamation mark ! is part of the sheet syntax in the formula, it used as a separator between the sheet name and range within it.
Hope that helps
I range does mean what I thought it did, but I haven't been able to understand how the range is figured between the two columns. If it was A12:A48 I understand that.
Thank You
This range A12:B48 is a group of cells (grid) within the sheet Receipt Types.
The formula used that syntax to refer to it.
The range starts from cell A12 to cell B48 which is 2 columns by 37 rows.
This is how the range looks like in the sheet when you highlight it: