Problem with formula PROCV

[jgalas]

Hello

My worksheet have several tables named: table1, tables2, tables3,...
the number # of the table depende on data in cell AF5(1,2,3,4,...)
I want, using the IF control, put in the formula PROCV(a1;table#;2) the right table name.

Anyone can help me with this...

Thanks

 

[jgalas]

I have this formula:


=PROCH(AF5;Folha;SE(AE5="CLC";2;SE(AE5="CP";3;SE(AE5="STC";4;""))))

That works perfectly.

But when i put it in a PROCV.. it dont work, i have a #value! error

=SE(PROCV($AF$2;PROCH(AF5;Folha;SE($AE$5="CLC";2;SE($AE$5="CP";3;SE($AE$5="STC";4;""))));4)="c";"X";"")

 

[jgalas]

No one!!!

There is a way to transform a text string to a name that Excel understands like a name of a table?

 

[jgalas]

Re: Problem with formula VLOOKUP

SORRY GUYS my version of excel it's no English, now i know why i have no answer.


The formula are:


=HLOOKUP(AF5;Folha;IF(AE5="CLC";2;IF(AE5="CP";3;IF(AE5="STC";4;""))))

Works

But when i put it in a VLOOKUP... it dont work, i have a #value! error

=IF(VLOOKUP($AF$2;HLOOKUP(AF5;Folha;IF($AE$5="CLC";2;IF($AE$5="CP";3;IF($AE$5="STC";4;""))));4)="c";"X";"")

 

[Norie]

Re: Problem with formula VLOOKUP

Is the formula you are using for the 2nd argument in the VLOOKUP returning a range?

 

[jgalas]

Re: Problem with formula VLOOKUP

Is the formula you are using for the 2nd argument in the VLOOKUP returning a range?

Yes, it should give the contents of a cell that represents the name of a table

 

[jgalas]

Re: Problem with formula VLOOKUP

Yes, it should give the contents of a cell that represents the name of a table

A table range like ---> AVAL2 ==> $AA$2:$AE$25 or AVAL6 ==> $AR$2:$AW$25, ...

 

[Norie]

Re: Problem with formula VLOOKUP

If it's only returning the name of the table you probably need to use INDIRECT to get a reference to the table itself.

 

[jgalas]

Re: Problem with formula VLOOKUP

If it's only returning the name of the table you probably need to use INDIRECT to get a reference to the table itself.

Can you help me with that, i dont know that fonction.
Thanks

 

[Norie]

Re: Problem with formula VLOOKUP

This is how you might use INDIRECT, but I can't guarantee this will work - perhaps you could tell us more about what you are trying to do.

Anyway here it is:

INDIRECT(HLOOKUP(AF5;Folha;IF(AE5="CLC";2;IF(AE5="CP";3;IF(AE5="STC";4;"")))))

If the HLOOKUP returns a valid name then that should return a reference to it.

By the way in the VLOOKUP why do you have the ="c" part?

HLOOKUP(AF5;Folha;IF(AE5="CLC";2;IF(AE5="CP";3;IF(AE5="STC";4;""))))="c"