Column lookup based on cell content
Column lookup based on cell content
(OP)
Hi all,
I am stuck trying to implement a function into excel. The part I am stuck on is using a lookup based on a cells content. I assume I want some use of the indirect function but cant quite make it to work.
The main issue I have is if the cell content of f2 is 1, I want to lookup D5:D31, if its 2 then I want to lookup E5:E31 etc.
The array I am trying to enter this change into is:
{=IF(ISERROR(INDEX('Gantt Chart'!$A$5:$AB$31,(SMALL(IF('Gantt Chart'!$D$5:$D$31>0,ROW('Gantt Chart'!$A$5:$A$31)),ROW(1:1)))-4,1)),"",INDEX('Gantt Chart'!$A$5:$AB$31,(SMALL(IF('Gantt Chart'!$D$5:$D$31>0,ROW('Gantt Chart'!$A$5:$A$31)),ROW(1:1)))-4,1))}
The trimmed back spreadsheet is attached.
Can anybody advise how to amend it?
Thanks
I am stuck trying to implement a function into excel. The part I am stuck on is using a lookup based on a cells content. I assume I want some use of the indirect function but cant quite make it to work.
The main issue I have is if the cell content of f2 is 1, I want to lookup D5:D31, if its 2 then I want to lookup E5:E31 etc.
The array I am trying to enter this change into is:
{=IF(ISERROR(INDEX('Gantt Chart'!$A$5:$AB$31,(SMALL(IF('Gantt Chart'!$D$5:$D$31>0,ROW('Gantt Chart'!$A$5:$A$31)),ROW(1:1)))-4,1)),"",INDEX('Gantt Chart'!$A$5:$AB$31,(SMALL(IF('Gantt Chart'!$D$5:$D$31>0,ROW('Gantt Chart'!$A$5:$A$31)),ROW(1:1)))-4,1))}
The trimmed back spreadsheet is attached.
Can anybody advise how to amend it?
Thanks





RE: Column lookup based on cell content
{=IF(ISERROR(INDEX('Gantt Chart'!$A$5:$AB$31,(SMALL(IF(INDIRECT("'Gantt Chart'!R5C"&$G$2+3,0):INDIRECT("'Gantt Chart'!R31C"&$G$2+3,0)>0,ROW('Gantt Chart'!$A$5:$A$31)),ROW(1:1)))-4,1)),"",INDEX('Gantt Chart'!$A$5:$AB$31,(SMALL(IF(INDIRECT("'Gantt Chart'!R5C"&$G$2+3,0):INDIRECT("'Gantt Chart'!R31C"&$G$2+3,0)>0,ROW('Gantt Chart'!$A$5:$A$31)),ROW(1:1)))-4,1))}
RE: Column lookup based on cell content
Could you give more details of what you are trying to do.
Doug Jenkins
Interactive Design Services
http://newtonexcelbach.wordpress.com/
RE: Column lookup based on cell content
Here's your normalized data for the tirst 3 Activity Nos...
...using the technique in this FAQ on Tek-Tips: http://www.tek-tips.com/faqs.cfm?fid=5287
Skip,
Just traded in my OLD subtlety...
for a NUance!