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 progra...

excel

Formulas and Functions

Macros and VBA

Haytham Amairah

Silver Contributor

Mar 31, 2020

pperry

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

pperry

Copper Contributor

Mar 31, 2020

Haytham Amairah

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.

Forum Discussion

Excel Formula

Resources