Index Function - Excel

[binaryfingers]

Hi

I use the following code to return a value from a cell.

=INDEX('YTD ''06 Analysis'!$B$5:$IT$500,46,(MATCH(Summary!$C$3,'YTD ''06 Analysis'!$B$5:$IT$5,0)))

This basically matches the column with a date designated on the summary sheet, then moves down 46 columns and pulls the data out.

However my requirement is different for a new project and I just cant get it cracked.

This time I know the column, 201, but I need to return the row number by looking up the 'key' on both sheets. (similar to vlookup) with this in mind I have looked at hlookup but I am struggling how to return the row number.

Is there someone that can help assist me?

Thanks,

 

[binaryfingers]

Hi

I have figured it out using the match command and adding the appropriate number or rows to that 'match'. Its all formatted the same way so the additional rows is always the same.

Thanks,

 

[SkipVought]

bf,

Keep in mind that MATCH returns a row offset that is RELATIVE to the referenced range.

Skip,

_[red][/red]

 

[firefytr]

So you should be able to amend your formula and only change the specifics of your formula ...

=INDEX('YTD ''06 Analysis'!$B$5:$IT$500,(MATCH(ValueToFind,'YTD ''06 Analysis'!$B$5:$B$500,0)),201)

The bold area is where you will look to find the ValueToFind. If you have a concatenated value from your two sheets, ensure that your ValueToFind is also concatenated in the formula. Post back if you need more help.

Regards,
Zack Barresse

Simplicity is the ultimate sophistication. What is a MS MVP? PODA
- Leonardo da Vinci