Offset within large

[namluke]

Hi, i have a large formula in column B looking up column H

Im looking to bring back data with an offset from column G into column A

I have put the examples highlighted in yellow.

Can this be done?

[canapone]

Hi,

maybe in a7 and below

=INDEX($G$7:$G$10,MATCH(B7,$H$7:$H$10,0))

Hope it helps

[namluke]

That works great but if the there is any of the same numbers it comes back with the same text.

[canapone]

Hi,

maybe array INDEX to be confirmed with control+shift+enter (very important)

=INDEX($G$7:$G$10,SMALL(IF($H$7:$H$10=B7,ROW($A$1:$A$4)),COUNTIF($B$7:B7,B7)))

Please see the attached file

Hope it helps

[namluke]

its seems to work with the amount of data within the spread sheet, but when I add more it stops working.

I have dragged down the arrays but it still comes up with NA - the thing that seems to effect it is the row part of the formula.

Any advice?

[canapone]

Hi,

I'd need again a sample file. maybe you did not confirm correctly the formulas.

Regards