IF statement containing index match.

[dougmarkham]

Hi guys,

I have this issue with a formula. Here is an outline of the task:


What I'm trying to do:

I am copy/pasting data from a source file into my data table then pulling certain columns from my data table into a report table (on the same worksheet). The trouble column in my data table has a field name: destroy old stock and cells contain either one of three values --- "0", "1", or "" (blank). Sounds simple right...

In my report table (in the corresponding column 'destroy old stock') there is a formula that converts zeros and ones into no's and yes':

• If the cell has a zero in my data table, I want the formula to return "no" in the report table;
• If the cell has a ONE, I want the formula to return "yes";
• If the cell is blank (""), I want the formula to return a blank.

I inherited the formula from my predecessor is as follows:

=IF(INDEX($J$1:$XFD$96495,ROW(A3),MATCH(I$1,$J$1:$XFD$1,0))="0","No",IF(INDEX($J$1:$XFD$96495,ROW(A3),MATCH(I$1,$J$1:$XFD$1,0))="1","Yes",""))

As I understand it, this is saying: in the range for data table, in this row...exactly match the report table header in cell I1 (destroy old stock) with data table headers in range J1:XFD1...then if the cell in the data table says zero, return "no" but if the cell in the data table says ONE, return "yes", or if blank, return blank.

Relevant fact: The source file comes from an *xls worksheet i.e., 97-2003 version of excel.

• The column data for 'destroy old stock' in the source file is different from all the other columns in the source file in that: it contains an error message for in each cell (each cell contains a small green arrowhead in the top left of the cell). The error message reads: "number stored as text". This is odd because when you click one of these cells and check the format, the formatting is set as 'general', not text. Also, double-clicking on one of these cells removes the 97-2003 formatting, causing the formula to return blanks for 1, 0 and blank.

See googledrive link for picture

https://drive.google.com/file/d/1JFN3GvKgufNvmZadE-3YN3yiyeTuixZB/view?usp=sharing

Issue:

• if I copy the 'destroy old stock' column data from the source file and 'paste with formatting' into the data table, then the formula works...however...
• if I copy the 'destroy old stock' column data from the source file and 'paste values' into the corresponding column in the data table, then the formula in the report table returns blanks.

See googledrive link for picture
https://drive.google.com/file/d/1nJpp8rzvLv6MaXW6oxUoZEd01OdF51Nr/view?usp=sharing

As you can see, the cells that I've double clicked into have lost this formatting and no longer return a value.
It appears that the 97-2003 excel file in the column 'destroy old stock' has some text formatting unique to the 97-2003 format that 2010 excel doesn't recognize as text formatting but for some reason, the formatting is required to make the formula work. Does this mean that the formula only works for the earlier versions of excel?

Is there a better formula for 2010 excel that would allow me to do the same thing?

 

[dougmarkham]

It only calls the INDEX(MATCH()) function once i.e. it's more efficient. Not that you'd be likely to notice the difference unless you were using the function in many many cells.

I see, so if I had 150,000 cells in the column, Rory's formula will finish first?!

Thanks for your help and explanations!

Kind regards,

Doug.

 

[FormR]

I see, so if I had 150,000 cells in the column, Rory's formula will finish first?!

Hi, I put both options into 1000 cells each with a random selection of 0's 1's and blanks and tested the relative speed of each using the workbook shg posted in post #13 here https://www.mrexcel.com/forum/excel-questions/897761-isblank-vs-len-most-efficient-2.html and the CHOOSE() option was approx. 1.5 X faster to calculate.

Again, whether you can tell the difference in the real world is another story - but why not just go for the faster one anyway.