Index Match SumIf Approximate Match

Good afternoon!

I have a workbook that has information generated from an ERP system. There is a tab that a consolidated report view of the summed amount for the current month, previous month, and ytd based on the account description. Is there a way to do a vlookup or index/match for an approximate match for the summed amount?

In Report tab, D3 has the value that I'm searching for in NIS CY tab.
If the column matches the corresponding value then give me the value(s) based on Account Description, ie, "Service Revenue" to "40000000 Service Revenue"

Any help would be greatly appreciated! Thanks!

Try array entering this in B6 of 'Report'. Then fill down and across.

If you aren’t familiar with array-entered formulas array enter means the formula must be committed from edit mode by simultaneously pressing and holding down Ctrl and Shift while hitting Enter.

Formula:

Please Login or Register  to view this content.

By the way the column headers in 'NIS CY' are not numbers like the headers in 'Report'. They are text that look like numbers. If you are not aware of it text has numeric value of 0. Note that in the formula -- precedes the range --'NIS CY'!$H$8:$S$8 in the formula. This is one way of coercing the text "numbers" into their underlying numeric values. Although it is better to correct these things at data entry.

You are welcome. Glad to help. Thank you for the feedback, added rep and marking your thread Solved.

It's hard for me to understand it right now but I'll let it digest overnight and see if I have a handle on it by morning time!

I don't blame you and kudos for your willingness to figure it out. Music to our ears.

In case it helps I found this site helpful for understanding these.

http://xldynamic.com/source/xld.SUMPRODUCT.html