SOLVED

How to use MATCH in a VLOOKUP formula with INDIRECT

%3CLINGO-SUB%20id%3D%22lingo-sub-3301480%22%20slang%3D%22en-US%22%3EHow%20to%20use%20MATCH%20in%20a%20VLOOKUP%20formula%20with%20INDIRECT%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-3301480%22%20slang%3D%22en-US%22%3E%3CP%3EI%20have%20a%20workbook%20with%20multiple%20sheets%20for%20different%20communities%2C%20and%20I%20am%20trying%20to%20create%20a%20consolidated%20sheet%20with%20all%20items.%20I%20need%20to%20pull%20a%20date%20from%20a%20column%20within%20each%20sheet%20and%20insert%20it%20into%20a%20column%20in%20the%20master%20sheet.%20I%20have%20had%20success%20with%20using%20a%20VLOOKUP%20formula%20with%20INDIRECT%20to%20find%20the%20sheet%20that%20I%20want%20to%20look%20on%2C%20but%20I%20cannot%20use%20a%20direct%20column%20reference%20because%20the%20column%20order%20can%20vary%20from%20sheet%20to%20sheet.%20I%20decided%20to%20try%20and%20use%20a%20MATCH%20formula%20to%20find%20the%20column%20number%20based%20on%20the%20column%20name%2C%20but%20I%20keep%20receiving%20a%20%23NA!%20error.%20I%20cannot%20seem%20to%20find%20where%20my%20error%20is.%20I%20have%20attached%20an%20example%20workbook%20with%20the%20broken%20formula%20still%20in%20cell%20E2%20on%20the%20%22Master%22%20tab.%20Can%20anyone%20tell%20me%20what%20I%20am%20doing%20wrong%3F%20Thank%20you%20in%20advance!%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThis%20is%20the%20formula%20that%20I'm%20not%20having%20success%20with%3A%3C%2FP%3E%3CP%3E%3CSTRONG%3E%3DVLOOKUP(%24B2%2CINDIRECT(%22'%22%26amp%3B%24A2%26amp%3B%22'!%24A%242%3A%24k%2420%22)%2CMATCH(%22Purchasing%20Final%20Phase%20PO%20Release%22%2CINDIRECT(%22'%22%26amp%3B%24A2%26amp%3B%22'!%24A%242%3A%24k%241%22)%2C0)%2CFALSE)%3C%2FSTRONG%3E%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-3301480%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EExcel%20for%20web%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EOffice%20365%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-3301538%22%20slang%3D%22en-US%22%3ERe%3A%20How%20to%20use%20MATCH%20in%20a%20VLOOKUP%20formula%20with%20INDIRECT%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-3301538%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F1213069%22%20target%3D%22_blank%22%3E%40Edg38426%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EHere%20it%20shall%20be%20A1%3C%2FP%3E%0A%3CP%3E%3CSTRONG%3E%3DVLOOKUP(%24B2%2CINDIRECT(%22'%22%26amp%3B%24A2%26amp%3B%22'!%24A%242%3A%24k%2420%22)%2CMATCH(%22Purchasing%20Final%20Phase%20PO%20Release%22%2CINDIRECT(%22'%22%26amp%3B%24A2%26amp%3B%22'!%24A%24%3CFONT%20color%3D%22%23DF0000%22%3E1%3C%2FFONT%3E%3A%24k%241%22)%2C0)%2CFALSE)%3C%2FSTRONG%3E%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-3301524%22%20slang%3D%22en-US%22%3ERe%3A%20How%20to%20use%20MATCH%20in%20a%20VLOOKUP%20formula%20with%20INDIRECT%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-3301524%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F1213069%22%20target%3D%22_blank%22%3E%40Edg38426%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EA%20very%20small%20error%20in%20your%20formula%3C%2FP%3E%3CP%3E%3CSTRONG%3E%3DVLOOKUP(%24B2%2CINDIRECT(%22'%22%26amp%3B%24A2%26amp%3B%22'!%24A%242%3A%24k%2420%22)%2CMATCH(%22Purchasing%20Final%20Phase%20PO%20Release%22%2CINDIRECT(%22'%22%26amp%3B%24A2%26amp%3B%22'!%24A%24%3CFONT%20color%3D%22%23DF0000%22%3E1%3C%2FFONT%3E%3A%24K%241%22)%2C0)%2CFALSE)%3C%2FSTRONG%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EYou%20might%20also%20consider%20removing%20the%20text%20from%20the%20MATCH%20function%20so%20it%20just%20refers%20to%20the%20column%20heading%3A%3C%2FP%3E%3CP%3E%3CSTRONG%3E%3DVLOOKUP(%24B2%2CINDIRECT(%22'%22%26amp%3B%24A2%26amp%3B%22'!%24A%242%3A%24k%2420%22)%2CMATCH(%3CFONT%20color%3D%22%23DF0000%22%3EE%241%3C%2FFONT%3E%2CINDIRECT(%22'%22%26amp%3B%24A2%26amp%3B%22'!%24A%241%3A%24K%241%22)%2C0)%2CFALSE)%3C%2FSTRONG%3E%3C%2FP%3E%3C%2FLINGO-BODY%3E
Occasional 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 (Occasional 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)