Match Function - lookup arry (making it dynamic) assistance required
Good day,
I have some difficulty in the match function.
when I do this it works perfectly..
=VLOOKUP($N$11,INDIRECT($AR$19),MATCH(L12&" Per meter",ACPIPESANS62HD,0))
--------------
but I have two tables the data can lay in. thus I want to make the "ACPIPESANS62HD"
dynamic.
I have a drop down menu in AR19 that "toggles" between the two tables (ACPIPESANS62 & ACPIPESANS719).
but to direct the match function's look up array, I can not seem to get the AR19&"HD" (contents set to ACPIPESANS62)to go to ACPIPESANS62HD table.
There seems to be a disconnect from reading if from a cell and hard typing it in the formula
I have tried to make a new cell that combines AR19 (which is selected to the ACPIPESANS62) & HD
which gives the ACPIPESANS62HD result in AR21. (=AR19&"HD")
but =VLOOKUP($N$11,INDIRECT($AR$19),MATCH(L12&" Per meter",AR21,0)) gives a #N/A result ?
Can you please give me some advice on how to fix it.
Solved it by adding
Solved it by adding "indirect" infront of AR21.
In my opinion, it should work without it, but it solved the problem, hence I can continue.
Can anyone indicate why that is?
Without using indirect the
Without using indirect the match function in your vlookup is searching for L12&" Per Meter" in cell AR21. Cell AR21 has a value only of ACPIPESANS62HD or ACPIPESANS719HD. Obviously it cannot find L12&" Per Meter" in either of these 2 strings of text.
When you enter indirect(AR21) as your match search area you are then telling it to find L12&" Per Meter" in the named area of either ACPIPESANS62HD or ACPIPESANS719HD and not just to look at the value held in Cell AR21
Hope that helps
Regards
Mark