Vlookup - criteria is using the result of an index match array - gets 0

[MixedUpExcel]

Hi All,

I have 2 formula's in 2 cells.

Cell 1 - is using iferror / index / match (concatenate)
Cell 2 - is a vlookup

Cell 1 returns the value (text) that I am expecting
Cell 2 returns a 0 when I am using the Cell 1 as the criteria for the lookup (this is not the correct result)

As I have confirmed Cell 1 with SHIFT + CTRL + ENTER, I'm assuming that the vlookup can't use the array as a criteria? or at least without something else in there to help.

Can anyone tell me how this is usually fixed so that result is correct and not 0?

Thanks.

Simon

 

[steve the fish]

In this case it would be helpful to see the formulas in question.

 

[MixedUpExcel]

Hi,

In this case, I'm not sure why the formula is relevant as I was just wondering if this was a common issue to do a look up using the result of an array as the criteria in a vlookup and how this would be usually fixed, however, here are the formulas:

The sheet name that both of these are on is ' Template '

Cell C7 -

=VLOOKUP(E7,'[CODES.xlsx]Fascias'!$A:$B,2,0)

Cell E7 -

{=IFERROR(INDEX('RIGID LH'!G:G,MATCH(CONCATENATE('O7,P7,Q7),'RIGID LH'!L:L,0)),"")}

Thanks.

 

[steve the fish]

The 2nd formula doesnt need to be array entered. The vlookup will happlily use the result in cell E7 for its lookup value. If the result is 0 then either the result really is a zero or the cell in column B of the Fascias sheet is blank.

 

[MixedUpExcel]

Hi Steve,

Thanks for the tip about not using an array, I'm so used to using Index Match Match... however, that didn't sort my issue but did lead to the solution.

What excel wasn't telling me initially was that there was a circular reference using the cell I was trying to put the formula into. When I typed my VLOOKUP in an unrelated cell, regardless of how I had confirmed the Index Match formula, it brought back the result I was expecting.. so the issue was 'circular reference'.

Thank you for your time in looking into this.

Simon