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


 

felipemcandido

New Member
Joined
Oct 31, 2019
Messages
4
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

Well-known Member
Joined
Dec 3, 2018
Messages
9,531
Office Version
2007
Platform
Windows
@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

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

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

Well-known Member
Joined
Dec 3, 2018
Messages
9,531
Office Version
2007
Platform
Windows
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

Well-known Member
Joined
Dec 3, 2018
Messages
9,531
Office Version
2007
Platform
Windows
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.
 

Forum statistics

Threads
1,084,748
Messages
5,379,613
Members
401,615
Latest member
syn_excel

Some videos you may like

This Week's Hot Topics

  • VBA code giving errors and stopping Excel
    Hello Experts, I have this code being used to loop through files in a file path, and copy specific data to another sheet. It is giving me several...
  • Disable MsgBox message
    Morning, I have a userform where if i leave a ComboBox empty i see a MsgBox warning me that i must enter an invoice number. It is this MsgBox i...
  • Macro Recorder into VBA, Copy Paste Data Filled Cells
    Hi Everyone, I have a macro recorder file that takes a selection of data, copies, then pastes into a new sheet on ("A2:B2") The issue is my...
  • Number format changes while pasting into a cell
    Hi, I am trying to paste a number 180204524303 from an email to an excel cell, however, whenever i try to do so , the the paste value appears as...
  • Collating data
    Hello all. Could someone please help. I am trying to pull all column data from multiple sheets (24 I total so far) into 1 master sheet without...
  • Sum Multiple Columns Based on Multiple Criteria
    I am trying to consolidate data by summing columns G through M based on material, plant, vendor, and fiscal year being identical. The period does...
Top