Forum Discussion
How to use MATCH in a VLOOKUP formula with INDIRECT
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)
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)
2 Replies
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)
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)
