Vlookup Indirect and Match function error

[ct00010]

Hi all,

I would like the formula to find the pricing across multiple tabs and the problem is the pricing column.

The pricing column is situated on a different column letter on each tab.

Would it be possible to replace the 1 from the formula to a match function? like the formula below:

=(VLOOKUP(C$3&"*",INDIRECT("'"&"*"&$B4&"*"&"'!$A:$Z"),MATCH("Price",0)

I cannot work out a way to return a value when a the price column are situated in different column letters and tabs across the workbook.

Is there a solution to this formula please?

Thank you in advance

CT

 

[felipemcandido]

Your MATCH function is incomplete.

=VLOOKUP(C$3&"*",INDIRECT("'"&"*"&$B4&"*"&"'!$A:$Z"),MATCH("Price",INDIRECT("'"&"*"&$B4&"*"&"'!$A1:$Z1"),0))

And it is missing a parenthesis from VLOOKUP.

 

[DanteAmor]

@ct00010, Taking as reference the formula of @felipemcandido, If you want the exact search, 0 is missing at the end of Vlookup

=VLOOKUP(C$3&"*",INDIRECT("'"&"*"&$B4&"*"&"'!$A:$Z"),MATCH("Price",INDIRECT("'"&"*"&$B4&"*"&"'!$A1:$Z1"),0),0)

 

[ct00010]

@ct00010, Taking as reference the formula of @felipemcandido, If you want the exact search, 0 is missing at the end of Vlookup

=VLOOKUP(C$3&"*",INDIRECT("'"&"*"&$B4&"*"&"'!$A:$Z"),MATCH("Price",INDIRECT("'"&"*"&$B4&"*"&"'!$A1:$Z1"),0),0)

Hi all,

This doesnt seem to work.

Please find attached file.

https://filebin.net/6dcx70tz51r8uojc

I've changed the sequence from $A1:$Z1 to $A:$Z as some tabs are shown on different rows.

Still doesn't seem to work

 

[ct00010]

Hey Feli,

Thanks for the formula, I've modified it slightly to adhere to changes in row number and it doesnt seem to pick some of the info up.

Am I doing something wrong? Please see below for my test file of the formula.

https://filebin.net/6dcx70tz51r8uojc

Thank in advance

 

[DanteAmor]

This part is to find the header in row 1.
It does not work to search in any row.


MATCH("Price",INDIRECT("'"&"*"&$B4&"*"&"'!$A1:$Z1"),0)

You can do a test where on the sheet the header is in row 1.

 

[DanteAmor]

Try this

=VLOOKUP(C$3&"*",INDIRECT("'"&"*"&$B4&"*"&"'!$A:$Z"),SUMPRODUCT((INDIRECT("'"&"*"&$B4&"*"&"'!$A1:$Z100")="Price")*(COLUMN(A:Z))),0) <tbody> </tbody>

<tbody>
</tbody>

With the above formula you will look for the "Price" title in the range A1:Z100, it is not recommended that in this type of search you put column A:Z, increase 100 if necessary.