CODEWITHSUNDEEP
excelexcel-formula
user7433793
user7433793

Reputation: 85

Excel Cell Reference

So I have a formula =address(2, match(I5, 2:2)) that returns a string "E2". When I try to nest my formula with in a =Hlookup ("A", address(2, match(I5, 2:2)):E1000,2,false), I get a error message.

I think I know why I'm getting an error message because i'm mixing up string and range reference here, but does anyone know how I can solve this issue?

I don't think that indirect is what I'm looking for here.

Thanks in advance.

Upvotes: 0

Views: 79

Answers (1)

Scott Craner
Scott Craner

Reputation: 152660

Instead of Address which returns a string that then needs to use INDIRECT to parse, use INDEX and save a step as well as remove the volatile functions of ADDRESS and the needed INDIRECT:

=Hlookup ("A", Index(2:2,match(I5, 2:2)):E1000,2,false)

But if you want the slower method of ADDRESS, which I would not recommend, then:

=Hlookup ("A", INDIRECT(address(2, match(I5, 2:2))&":E1000"),2,false),

Upvotes: 2

Related Questions