Indirect function not working properly

**zuri125** · 12-12-2012, 10:10 AM

Both of the codes below are doing the same procedure, in trying to find a corresponding result to your specified lookup value. As an example, I have picked out the maximum price from historical data, and now I want to find out what quarter of the year corresponds to that specified maximum price.

Using Lookup:
=LOOKUP(L4,INDIRECT(CONCATENATE("'",$D4,"'!G$151:G$163")),INDIRECT(CONCATENATE("'",$D4,"'!B$151:B$163")))

Using Index/Match:
=INDEX(INDIRECT(CONCATENATE("'",$D4,"'!B$151:B$163")), MATCH(L4, INDIRECT(CONCATENATE("'",$D4,"'!G$151:G$163"))))

Where L4 is the lookup value I am trying to find in the historical data (the specified maximum price).
D4 contains the name of the worksheet where the historical data exists.
G151:G163 contains the prices where L4 was originally derived from.
B151:B163 is the list that corresponds to G151:G163, and what I want my output to be (eg. Quarter of the year).

I have to you the indirect since I have data on multiple worksheets that have the same template. So I can use the range 151:163 on every worksheet, but simply need the worksheet name (D4) to change.

Troubleshooting:

I am led to believe that this issue is cause by the Indirect function and its volatile characteristic.

With some worksheets/data, this formula works just fine. With other ones, I get mistakes with the formula. So even though my maximum price is located in G151 for example, and should get the value returned from B151, I instead get the value of B163. Which in the way that Lookup works, this basically means, my maximum price was never found at all. (Right?)

Decomposing my formulas, I find that problem in my Index/Match formula, actually occurs in the Match component. So, =MATCH(L4, INDIRECT(CONCATENATE("'",$D4,"'!G$151:G$163")), returns the last value again, of G163, when in fact it should be G151.

On occasion, with no change to the formula, it sometimes results in #N/A. This may be a related or isolated issue - I don't know.

I can confirm that the value in L4 and G151 are identical through a boolean test.

What boggles me is that it works for about half of my worksheets but not the other half. At this point, I've ran out of debugging ideas!

**JBeaucaire** · 12-12-2012, 11:38 AM

LOOKUP is a "fuzzy match" function, if it doesn't find the exact match, it finds the last cell with a value LESS than the match value. It may not be appropriate.

VLOOKUP is a fuzzy match too when the 4th parameter is 1 (or omitted). If the 4th parameter is 0 (must be included) then it allows exact matches only.

MATCH a fuzzy match too when the 3rd parameter is 1 (or omitted). If the 4th parameter is 0 (must be included) then it allows exact matches only.

See if that helps you correct your formulas.

If not, post a sample workbook showing examples of data that demonstrates working and non-working formulas so we can see the problem directly and suggest a solution we can test before providing it.

**Moo the Dog** · 12-12-2012, 11:43 AM

I tested your original formula on personally created data and it works fine. If you wanted to, it could be shortened to:

=INDEX(INDIRECT("'"&$D4&"'!B$151:B$163"),MATCH(L4,INDIRECT("'"&$D4&"'!G$151:G$163"),0))

Since you don't need the CONCATENATE function, just use &

But it worked both ways. So I would suggest uploading a copy of your workbook that has sheets where it IS working and, more importantly, where it ISN'T working, so we can take a look at it. Obviously, delete any sensitive information if necessary.

- Moo

**zuri125** · 12-12-2012, 01:01 PM

Thanks guys, I have resolved this.

I too soon after found my mistake with the Match function, that I need a 0 as the 4th parameter for an exact value match. Not the first time this has slipped by!

Still, I can't resolve the Lookup function. JBeau, as you put it, it is certainly a fuzzy function. Good thing I only needed one of these to work.

Moo, thanks for reminding me how to combine strings without concatenate - I'll do that next time.

So my follow up question now is, that the match function find the FIRST time the value comes up. So if the same value appears right afterwards, it will only return that first value. I am curious to learn if there is another method of finding the LAST time the value appears. This could help expand my analysis.

Thanks for the help so far!