Index formula returning blank value.

**Talk_4** · 01-19-2015, 04:20 PM

I'm using an index formula that's referencing inventory items, and all the item numbers seem to work with the exception of one: "34.02"

I'm not sure why that is, but allow me to explain my excel workbook set-up:
Imagine columns A through E:

• A1 is the item number “34.02”

• B1 is the vendor selected (P, K, C or F) – abbreviations only, referenced from another spreadsheet in the workbook.

o =IF(C1="",INFO!F13,"") – note, I’m pulling the P, K, C or F from another spreadsheet in the workbook.

• C1 is the substitute vendor if B1 didn't carry the item selected, which would leave B1 blank if C1 was filled with either P, K, C or F – This allows for the same vendor down the list with the exception of one to be changed, if needed.

• D1 is the hidden the hidden column that displays the item number depending on the vendor selected for columns B1 or C1. Example:

o A1 is 31.02 and if B1 is P, then we add “0” to the item number giving us 31.02, but, if B1 is K, we add “+1” = 32.04, C “+2”, and lastly F we add “+3” as follows.

o If B1 is left blank due to vendor shortage, we fill in column C with the appropriate vendor abbreviation, leaving B1 blank and C1 filled. Again, this allows us to keep the same vendor down the row list with the exception of need, if needed.

• Lastly, E1 has the =INDEX formula and for some odd reason, 34.02 returns an error or blank, depending on how you write your formula. Here’s mine with an explanation:

o =IF(ISNA(INDEX('[NewMasterInventory.xls]Master Inventory'!$C$1:$C$1366,MATCH(D1,'[NewMasterInventory.xls]Master Inventory'!$A$1:$A$1366,0))),"",INDEX('[NewMasterInventory.xls]Master Inventory'!$C$1:$C$1366,MATCH(D1,'[NewMasterInventory.xls]Master Inventory'!$A$1:$A$1366,0)))

o I’m pulling the information from another workbook where the inventory items are listed. As you can see I have 1366 rows to go through, and of all the item numbers work, only 34.02 doesn’t.

• Now comes the tricky part. If I placed “34.02” in D1 without having the formula populate it for me depending on vendor selected, it works, however, if I just leave it alone and allow the formula from D1 to auto-generate the number depending on what was entered the columns A-C, it gives me an error/blank.

Please, someone, let me know if there is something wrong with my reference formula set up, or if there is simply a ghost in my Excel 2013 app.

**mrice** · 01-19-2015, 04:27 PM

it's difficult to help without rebuilding your sheet - suggest attaching a copy.

You might try using the evaluate button on the formula ribbon to step through the evaluation one step at a time. This will show you at what stage the unexpected result is occurring.

**mrice** · 01-21-2015, 04:11 AM

Looks like a bizarre rounding error. As you already have the maximum amount of nesting in your formula in column D (so we can't add an additional rounding function), I would suggest an alternative strategy using a user defined function.

Add the following code in module1

Please Login or Register  to view this content.

In D32 enter

=ROUND(ForumSpecial(A32,B32,C32),2)

User defined functions are very useful for getting over problems where the level of nesting is maxed out.