Forum Discussion

tanvirabid's avatar
tanvirabid
Copper Contributor
Aug 21, 2019

Indirect vlookup match

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.

Formulas and Functions

4 Replies

  • Twifoo's avatar
    Twifoo
    Silver Contributor
    Aug 22, 2019

    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)

     

  • Kodipady's avatar
    Kodipady
    Iron Contributor
    Aug 21, 2019

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