Forum Discussion

kallima_butterfly's avatar
kallima_butterfly
Copper Contributor
Oct 14, 2023

I need excel to recognize what is in a cell and return the appropriate value from another cell

Hi   I am trying to build a spreadsheet where once a certain value is inputted it triggers 3 other cells to input the values corresponding to that value.  It's a pay sheet. So I want to enter the t...
Formulas and Functions
OliverScheurich's avatar
OliverScheurich
Gold Contributor
Oct 14, 2023

kallima_butterfly 

=INDEX($G$3:$L$24,MATCH($A$17,$F$3:$F$24,0),MATCH(1,($G$1:$L$1=B$16)*($G$2:$L$2=$A$18),0))

I'd use a data layout as shown in the screeshot. The formula has to be entered with ctrl+shift+enter if one doesn't work with Office 365 or Excel for the web or Excel 2021.

  • kallima_butterfly's avatar
    kallima_butterfly
    Copper Contributor
    Oct 14, 2023

    OliverScheurich 

     

    Thank you so much. I changed the parameter a little but now I get "#N/A". Wondering where I went wrong?

     

    =INDEX('Tile & Civil'!$D$9:$M$48,MATCH($A$17,'Tile & Civil'!$A$9:$A$45,0),MATCH(1,('Tile & Civil'!$C$9:$C$48=K$17)*('Tile & Civil'!$D$8:$L$8=$B$17),0))

    • OliverScheurich's avatar
      OliverScheurich
      Gold Contributor
      Oct 14, 2023

      kallima_butterfly 

      =INDEX('Tile & Civil'!$D$9:$M$48,MATCH(1,('Tile & Civil'!$A$9:$A$48=$A$17)*('Tile & Civil'!$C$9:$C$48=K$17),0),MATCH($B$17,'Tile & Civil'!$D$8:$M$8,0))

       

      You are welcome. Without seeing your file or a screenshot i can only take a guess. Does the attached file help? The formula has to be entered with ctrl+shift+enter if one doesn't work with Office 365 or Excel for the web or Excel 2021.

Resources