Vlookup Indirect and Match function error

ct00010

New Member
Joined
Oct 22, 2019
Messages
9
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


 

Excel Facts

How to find 2nd largest value in a column?
MAX finds the largest value. =LARGE(A:A,2) will find the second largest. =SMALL(A:A,3) will find the third smallest
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.
 
Upvote 0
@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)
 
Upvote 0
@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 :(
 
Upvote 0
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
 
Upvote 0
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.

 
Upvote 0
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.
 
Upvote 0

Forum statistics

Threads
1,212,927
Messages
6,110,696
Members
448,293
Latest member
jin kazuya

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top