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

Format cells as time
Select range and press Ctrl+Shift+2 to format cells as time. (Shift 2 is the @ sign).
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
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,223,236
Messages
6,170,912
Members
452,366
Latest member
TePunaBloke

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