Home

Indirect vlookup match

%3CLINGO-SUB%20id%3D%22lingo-sub-816218%22%20slang%3D%22en-US%22%3EIndirect%20vlookup%20match%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-816218%22%20slang%3D%22en-US%22%3E%3CP%3EI%20have%20two%20sheets%20for%202%20assets%3A%2013100%20and%2013200.%20I%20have%20monthly%20numbers%20(i.e.%20Jan%2C%20Feb%20etc)%20for%20several%20accounts.%20I%20am%20trying%20to%20create%20a%20formula%20to%20get%20the%20numbers%20for%20a%20particular%20month%20for%20a%20particular%20asset.%20I%20am%20trying%20to%20use%20the%20indirect%20function.%20I%20have%20created%20%22Data%22%20in%20the%20Name%20manager.%20But%20my%20formula%20isn't%20working%3A%20%3DVLOOKUP(%24A5%2CINDIRECT(%22%22%26amp%3B%24B5%26amp%3B%22!Data%22)%2CMATCH(C%242%2CINDIRECT(%22%22%26amp%3B%24B5%26amp%3B%22!Data%22%2C0)%2C0))%3CBR%20%2F%3EThere%20seems%20to%20be%20an%20issue%20in%20the%20formula%20after%20the%20Match%20i.e.%20the%20second%20indirect.%20It%20is%20not%20connecting%20the%20month%20to%20the%20asset.%20The%20excel%20file%20is%20attached.%20Would%20appreciate%20if%20someone%20can%20assist.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-816218%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-816451%22%20slang%3D%22en-US%22%3ERe%3A%20Indirect%20vlookup%20match%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-816451%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F349140%22%20target%3D%22_blank%22%3E%40tanvirabid%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3Eplease%20check%20following%20formula%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3DVLOOKUP(%24A5%2CINDIRECT(%20%24B5%26amp%3B%22!Data%22)%2C%20MATCH(C%242%2C%20INDEX(%20INDIRECT(%20%24B5%26amp%3B%22!Data%22)%2C1%2C)%2C0%20)%2C%200)%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThe%20index%20formula%20extracts%20only%20first%20row%20from%20Data%20for%20MATCH%20formula.%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ELet%20me%20know%20how%20it%20works%20!!%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-816545%22%20slang%3D%22en-US%22%3ERe%3A%20Indirect%20vlookup%20match%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-816545%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F349140%22%20target%3D%22_blank%22%3E%40tanvirabid%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThis%20formula%20in%20C5%20should%20work%20for%20you%3A%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSTRONG%3E%3DVLOOKUP(%24A5%2C%3C%2FSTRONG%3E%3CBR%20%2F%3E%3CSTRONG%3EINDIRECT(%24B5%26amp%3B%22!Data%22)%2C%3C%2FSTRONG%3E%3CBR%20%2F%3E%3CSTRONG%3EMATCH(C%242%2CINDIRECT(%24B5%26amp%3B%22!%24A%244%3A%24H%244%22)%2C0)%2C0)%3C%2FSTRONG%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-817996%22%20slang%3D%22en-US%22%3ERe%3A%20Indirect%20vlookup%20match%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-817996%22%20slang%3D%22en-US%22%3E%3CP%3EMany%20thanks%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F368896%22%20target%3D%22_blank%22%3E%40Kodipady%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3EIt%20is%20working%20perfectly.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-818004%22%20slang%3D%22en-US%22%3ERe%3A%20Indirect%20vlookup%20match%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-818004%22%20slang%3D%22en-US%22%3E%3CP%3EThanks%20a%20lot.%20This%20is%20also%20working.%26nbsp%3B%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F280482%22%20target%3D%22_blank%22%3E%40Twifoo%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E
tanvirabid
New Contributor

I have two sheets for 2 assets: 13100 and 13200. I have monthly numbers (i.e. Jan, Feb etc) for several accounts. I am trying to create a formula to get the numbers for a particular month for a particular asset. I am trying to use the indirect function. I have created "Data" in the Name manager. But my formula isn't working: =VLOOKUP($A5,INDIRECT(""&$B5&"!Data"),MATCH(C$2,INDIRECT(""&$B5&"!Data",0),0))
There seems to be an issue in the formula after the Match i.e. the second indirect. It is not connecting the month to the asset. The excel file is attached. Would appreciate if someone can assist.

4 Replies

@tanvirabid 

please check following formula 

 

=VLOOKUP($A5,INDIRECT( $B5&"!Data"), MATCH(C$2, INDEX( INDIRECT( $B5&"!Data"),1,),0 ), 0)

 

The index formula extracts only first row from Data for MATCH formula. 

 

Let me know how it works !! 

@tanvirabid 

This formula in C5 should work for you: 

=VLOOKUP($A5,
INDIRECT($B5&"!Data"),
MATCH(C$2,INDIRECT($B5&"!$A$4:$H$4"),0),0)

 

Many thanks@Kodipady 

It is working perfectly.

Thanks a lot. This is also working. @Twifoo 

Related Conversations
Stable version of Edge insider browser
HotCakeX in Discussions on
35 Replies
flashing a white screen while open new tab
cntvertex in Discussions on
13 Replies
Tabs and Dark Mode
cjc2112 in Discussions on
22 Replies
How to Prevent Teams from Auto-Launch
chenrylee in Microsoft Teams on
28 Replies
Teams Calling - Dial pad missing
Chris Cooper in Microsoft Teams on
73 Replies