SOLVED

How to use MATCH in a VLOOKUP formula with INDIRECT

Brass Contributor

I have a workbook with multiple sheets for different communities, and I am trying to create a consolidated sheet with all items. I need to pull a date from a column within each sheet and insert it into a column in the master sheet. I have had success with using a VLOOKUP formula with INDIRECT to find the sheet that I want to look on, but I cannot use a direct column reference because the column order can vary from sheet to sheet. I decided to try and use a MATCH formula to find the column number based on the column name, but I keep receiving a #NA! error. I cannot seem to find where my error is. I have attached an example workbook with the broken formula still in cell E2 on the "Master" tab. Can anyone tell me what I am doing wrong? Thank you in advance!

 

This is the formula that I'm not having success with:

=VLOOKUP($B2,INDIRECT("'"&$A2&"'!$A$2:$k$20"),MATCH("Purchasing Final Phase PO Release",INDIRECT("'"&$A2&"'!$A$2:$k$1"),0),FALSE)

2 Replies
best response confirmed by Edg38426 (Brass Contributor)
Solution

@Edg38426 

 

A very small error in your formula

=VLOOKUP($B2,INDIRECT("'"&$A2&"'!$A$2:$k$20"),MATCH("Purchasing Final Phase PO Release",INDIRECT("'"&$A2&"'!$A$1:$K$1"),0),FALSE)

 

You might also consider removing the text from the MATCH function so it just refers to the column heading:

=VLOOKUP($B2,INDIRECT("'"&$A2&"'!$A$2:$k$20"),MATCH(E$1,INDIRECT("'"&$A2&"'!$A$1:$K$1"),0),FALSE)

@Edg38426 

Here it shall be A1

=VLOOKUP($B2,INDIRECT("'"&$A2&"'!$A$2:$k$20"),MATCH("Purchasing Final Phase PO Release",INDIRECT("'"&$A2&"'!$A$1:$k$1"),0),FALSE)

 

1 best response

Accepted Solutions
best response confirmed by Edg38426 (Brass Contributor)
Solution

@Edg38426 

 

A very small error in your formula

=VLOOKUP($B2,INDIRECT("'"&$A2&"'!$A$2:$k$20"),MATCH("Purchasing Final Phase PO Release",INDIRECT("'"&$A2&"'!$A$1:$K$1"),0),FALSE)

 

You might also consider removing the text from the MATCH function so it just refers to the column heading:

=VLOOKUP($B2,INDIRECT("'"&$A2&"'!$A$2:$k$20"),MATCH(E$1,INDIRECT("'"&$A2&"'!$A$1:$K$1"),0),FALSE)

View solution in original post