Log In

Come Join Us!

Are you an
Engineering professional?
Join Eng-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Eng-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.


Column lookup based on cell content

Column lookup based on cell content

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?


RE: Column lookup based on cell content

Nevermind I managed to solve this in the end, for anybody else looking, solution is:

{=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

There must be a simpler way!

Could you give more details of what you are trying to do.

Doug Jenkins
Interactive Design Services

RE: Column lookup based on cell content

FYI, Analyzing data in a Pivot (your Gantt) is very awkward. Best to Normalize your data, where you can use Excel's many data analysis tools effectively.

Here's your normalized data for the tirst 3 Activity Nos...

Activity No	Activity	Week	Value
01      	A       	1	1
01      	A       	2	1
02      	B       	4	1
02      	B       	5	1
02      	B       	6	1
02      	B       	7	1
03      	sdfg    	3	1
03      	sdfg    	4	1
03      	sdfg    	5	1
03      	sdfg    	6	1
03      	sdfg    	7	1
...using the technique in this FAQ on Tek-Tips:


glassesJust traded in my OLD subtlety...
for a NUance!tongue

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Eng-Tips Forums free from inappropriate posts.
The Eng-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Eng-Tips forums is a member-only feature.

Click Here to join Eng-Tips and talk with other members!


Close Box

Join Eng-Tips® Today!

Join your peers on the Internet's largest technical engineering professional community.
It's easy to join and it's free.

Here's Why Members Love Eng-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close