Bring value from another cell to the formula without being String

Question

The formula in excel PROCV vertically search for a value in a specific table of an X column.

Having said that, I have the following problem with this formula:

=PROCV(J7;JUL;6;JUL!C4:C35)

With this I want this formula to be dynamic, where through function =hoje() the field where the formula =PROCV(J7;JUL;6;JUL!C4:C35) is, be fed the correct month, but in doing so, seeking the value of another cell, is assigned String, causing the formula to find an error, as it is not allowed string in the formula (in this part in the case) because it would be the interval where the data I need, are.

Example:

When inserting the formula according to the image, the received data is like this:

=PROCV(43651;$T$7;6;"JUL!C4:C35")

On the field T7 I have a condition where I do the checking, if the current month is 1, then the output is "JAN", if it is 2, then the output is "FEV" and so on until "TEN".

On the field T8 i concateno the result of the cell T7 with the interval I need that is !C4:35.

The result brings two errors...

---

1º - Where should be the table matrix are receiving $T$7. Should receive the table in question, in case JUL (which is the current month)

2º - Where should receive the data interval is coming as string. You should get it without the quotation marks, for formula not finding error.

Having as the final result of the formula thus:

=PROCV(43651;JUL;6;JUL!C4:C35)

And it’s coming out like this:

=PROCV(43651;$T$7;6;"JUL!C4:C35")

---

How to solve this ?

Answer 1

Marcos, good afternoon!

I think the formula has the variables reversed in your example. I believe the right way would be:

=PROCV($T$7;INDIRETO(T8);6;0)

Run a test, you might need to use the INDIRETO in the $T$7 also.

Abs!