Any way to shorten this up?

[Guest]

Hi all, TIA.. had a user send me a sheet today, they wish to add 5 more
values to look up. The problem is that the answer that this formula comes
from are set up horizontally, the target cells are vertical so no autofill.
The formula is such:

=IF(HLOOKUP('Price Performance Index'!B6,'Price Performance
Index'!C618,3,FALSE)="N/A","FUTURE",IF(HLOOKUP('Price Performance
Index'!B6,'Price Performance Index'!C618,7,FALSE)=0,HLOOKUP('Price
Performance Index'!B6,'Price Performance
Index'!C618,5,FALSE),IF(HLOOKUP('Price Performance Index'!B6,'Price
Performance Index'!C618,5,FALSE)="N/A","N/A",IF(HLOOKUP('Price Performance
Index'!B6,'Price Performance Index'!C618,5,FALSE)>=(HLOOKUP('Price
Performance Index'!B6,'Price Performance
Index'!C618,7,FALSE)),"GREEN",IF(HLOOKUP('Price Performance
Index'!B6,'Price Performance Index'!C618,5,FALSE)<(HLOOKUP('Price
Performance Index'!B6,'Price Performance
Index'!C618,8,FALSE)),"RED","YELLOW")))))

There's got to be a way to shorten this up and repeat it across without
duplicating this nightmare?

Again, TIA.

Kevin M.

 

[Harlan Grove]

Kevin M wrote...

. . . had a user send me a sheet today, they wish to add 5 more
values to look up. The problem is that the answer that this formula comes
from are set up horizontally, the target cells are vertical so no autofill.

Maybe no AUTOfill, but still possible to drag to fill. That is, maybe
they can't double click on the fill handle, but they could still drag
it right.

The formula is such:

=IF(HLOOKUP('Price Performance Index'!B6,'Price Performance
Index'!C618,3,FALSE)="N/A","FUTURE",IF(HLOOKUP('Price Performance
Index'!B6,'Price Performance Index'!C618,7,FALSE)=0,HLOOKUP('Price
Performance Index'!B6,'Price Performance
Index'!C618,5,FALSE),IF(HLOOKUP('Price Performance Index'!B6,'Price
Performance Index'!C618,5,FALSE)="N/A","N/A",IF(HLOOKUP('Price Performance
Index'!B6,'Price Performance Index'!C618,5,FALSE)>=(HLOOKUP('Price
Performance Index'!B6,'Price Performance
Index'!C618,7,FALSE)),"GREEN",IF(HLOOKUP('Price Performance
Index'!B6,'Price Performance Index'!C618,5,FALSE)<(HLOOKUP('Price
Performance Index'!B6,'Price Performance
Index'!C618,8,FALSE)),"RED","YELLOW")))))

There's got to be a way to shorten this up and repeat it across without
duplicating this nightmare?

About the only thing you could do to shorten this would be replacing
the references to 'Price Performance Index'!B6 and 'Price Performance
Index'!C618 with defined names, being careful to make range addresses
in those defined names RELATIVE if that's what's needed, and replace
the FALSE 4th arguments to HLOOKUP with equivalent 0.

 

[Guest]

Thanks as always Harlan.. I'll try setting 'Price Performance Index'!C618
as static as the array data doesn't change from cell to cell jsut the index
number.

Kevin M.