Forum Discussion
Excel Formula
Hello Everyone,
I am fairly new to using Excel and just had something dropped in my lap that I have to deal with. We had a former employee create a form that autofills with information from a program. I have the formula that he used, but I don't know what all it means.
=IF(ISNA(VLOOKUP(A102,'Receipt Types'!$A$12:$B$48,2,FALSE)),0,VLOOKUP(A102,'Receipt Types'!$A$12:$B$48,2,FALSE))
I know that it looking in certain fields for the information, but I'm not sure what the $ and ! are used for. Any help would be greatly appreciated.
Thank You
8 Replies
Since you acknowledge being "fairly new to using Excel" let me add to the direct answers you've gotten the general suggestion that, in addition to acquiring a basic book or two on Excel, you become familiar with websites like the following: https://exceljet.net/
Here's a page where I've already searched the term "absolute" where you'll find what the "$" does, in the bigger context: https://www.youtube.com/watch?v=sHfWRb2yUrM
And here's one that includes what the "!" means, in giving an address in another sheet: https://exceljet.net/search?query=referring+to+other+sheet
Excel is a marvelous tool, but it does challenge with a learning curve. You've discovered here in the techcommunity a good resource. Don't hesitate to make use of it.
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
- Hello,
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 YouThis 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:
Hello,
I'm sorry, I don't understand by range A12:B48. Is this still from the sheet Receipt Types? I have that sheet right in front of me, but I'm confused by the range. I really have no experience with Excel, except for simple reports that I manually create, and complete the fields myself.
Thank You.
pperry The core of this formula is the VLOOKUP - that's the part that looks for a value on another sheet. This one takes the value from A102 and looks for it in the A column of the Receipt Types sheet, and returns the corresponding value from column B.
If that fails, it will return an N/A error. The IF / ISNA parts are just there to make the formula return a 0 instead of that.
The ! are how references to other sheets are written. The $ help with formula copy-pasting. If you copy a formula like =A1 down to the next cell, you get =A2. But with $s in there it doesn't change. That lets you copy the formula down on your current sheet without moving the references to the table on the other sheet too.
