# Vlookup Indirect and Match function error

#### ct00010

##### New Member
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?

CT

#### felipemcandido

##### New Member

=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
@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
@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.

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
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

#### DanteAmor

##### Well-known Member
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
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.

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

### 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...