SOLVED
Home

Fetch specific cell value which matches the RowName and ColumnName

%3CLINGO-SUB%20id%3D%22lingo-sub-580650%22%20slang%3D%22en-US%22%3EFetch%20specific%20cell%20value%20which%20matches%20the%20RowName%20and%20ColumnName%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-580650%22%20slang%3D%22en-US%22%3E%3CP%3EIs%20it%20possible%20to%20create%20a%20%22Excel%20formula%22%20where%20it%20returns%20the%20value%20of%20the%20cell%20(or)%20row%20number%20and%20column%20number%20when%20it%20matches%20with%20the%20specific%20row%20name%20and%20column%20name.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EFor%20example%3AI%20need%20value%20918%20or%20row%20number%20and%20column%20number%20of%20the%20cell%20where%20the%20row%20name%20is%20%22AMS%22%20and%20column%20name%20is%20%22MRO4%22.%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20style%3D%22width%3A%20364px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Fgxcuf89792.i.lithium.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F114145i8342190929D7E304%2Fimage-size%2Flarge%3Fv%3D1.0%26amp%3Bpx%3D999%22%20alt%3D%22Excel.PNG%22%20title%3D%22Excel.PNG%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20can't%20able%20to%20fetch%20the%20value%20using%20row%20and%20column%20number%20because%20each%20time%20new%20column%20and%20row%26nbsp%3B%20is%20added%20and%20table%20structure%20is%20changing%20so%20does%20the%20row%20and%20column%20number%20values.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-580650%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EExcel%20Online%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-580750%22%20slang%3D%22en-US%22%3ERe%3A%20Fetch%20specific%20cell%20value%20which%20matches%20the%20RowName%20and%20ColumnName%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-580750%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F341858%22%20target%3D%22_blank%22%3E%40suthandira%3C%2FA%3E%26nbsp%3B%2C%20for%20such%20sample%3C%2FP%3E%0A%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20style%3D%22width%3A%20531px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Fgxcuf89792.i.lithium.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F114147i84AC497CEC58EA71%2Fimage-size%2Flarge%3Fv%3D1.0%26amp%3Bpx%3D999%22%20alt%3D%22image.png%22%20title%3D%22image.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%0A%3CP%3Eit'll%20be%3C%2FP%3E%0A%3CPRE%3E%3DGETPIVOTDATA(%22%5BMeasures%5D.%5BSum%20of%20C%5D%22%2C%24F%245%2C%22%5BTable1%5D.%5BA%5D%22%2C%22%5BTable1%5D.%5BA%5D.%26amp%3B%5BMRO2%5D%22%2C%22%5BTable1%5D.%5BB%5D%22%2C%22%5BTable1%5D.%5BB%5D.%26amp%3B%5BAMS%5D%22)%3C%2FPRE%3E%0A%3CP%3EThe%20easiest%20way%20to%20generate%20the%20formula%20-%20type%20%22%3D%22%20in%20any%20empty%20cell%20and%20click%20on%20proper%20cell%20within%20your%20PivotTable.%20After%20that%20you%20may%20play%20with%20adjustments.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-581158%22%20slang%3D%22en-US%22%3ERe%3A%20Fetch%20specific%20cell%20value%20which%20matches%20the%20RowName%20and%20ColumnName%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-581158%22%20slang%3D%22en-US%22%3EThank%20you%20soo%20much%20it%20works%20for%20pivot%20table%20but%20how%20can%20we%20fetch%20value%20if%20the%20table%20is%20not%20a%20pivot%20table(Normal%20Table).Is%20it%20possible%20to%20get%20the%20value%3F%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-581254%22%20slang%3D%22en-US%22%3ERe%3A%20Fetch%20specific%20cell%20value%20which%20matches%20the%20RowName%20and%20ColumnName%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-581254%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F341858%22%20target%3D%22_blank%22%3E%40suthandira%3C%2FA%3E%26nbsp%3B%2C%20yes%2C%20for%20the%20table2%20like%20this%3C%2FP%3E%0A%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20style%3D%22width%3A%20342px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Fgxcuf89792.i.lithium.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F114159i1C1E451E21F60B6E%2Fimage-size%2Flarge%3Fv%3D1.0%26amp%3Bpx%3D999%22%20alt%3D%22image.png%22%20title%3D%22image.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%0A%3CP%3Ethat%20is%3C%2FP%3E%0A%3CPRE%3E%3DINDEX(Table2%5B%23All%5D%2C%0A%20%20%20MATCH(%22MRO2%22%2CTable2%5B%5B%23All%5D%2C%5BRow%20Labels%5D%5D%2C0)%2C%0A%20%20%20MATCH(%22AMS%22%2CTable2%5B%23Headers%5D%2C0))%3C%2FPRE%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-581306%22%20slang%3D%22en-US%22%3ERe%3A%20Fetch%20specific%20cell%20value%20which%20matches%20the%20RowName%20and%20ColumnName%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-581306%22%20slang%3D%22en-US%22%3EThank%20you%20soo%20much%20for%20helping%20me%20%3A)%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-581323%22%20slang%3D%22en-US%22%3ERe%3A%20Fetch%20specific%20cell%20value%20which%20matches%20the%20RowName%20and%20ColumnName%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-581323%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F341858%22%20target%3D%22_blank%22%3E%40suthandira%3C%2FA%3E%26nbsp%3Byou%20are%20welcome%3C%2FP%3E%3C%2FLINGO-BODY%3E
suthandira
New Contributor

Is it possible to create a "Excel formula" where it returns the value of the cell (or) row number and column number when it matches with the specific row name and column name.

 

For example:I need value 918 or row number and column number of the cell where the row name is "AMS" and column name is "MRO4".

Excel.PNG

 

I can't able to fetch the value using row and column number because each time new column and row  is added and table structure is changing so does the row and column number values.

 

5 Replies
Solution

@suthandira , for such sample

image.png

it'll be

=GETPIVOTDATA("[Measures].[Sum of C]",$F$5,"[Table1].[A]","[Table1].[A].&[MRO2]","[Table1].[B]","[Table1].[B].&[AMS]")

The easiest way to generate the formula - type "=" in any empty cell and click on proper cell within your PivotTable. After that you may play with adjustments.

Highlighted
Thank you soo much it works for pivot table but how can we fetch value if the table is not a pivot table(Normal Table).Is it possible to get the value?

@suthandira , yes, for the table2 like this

image.png

that is

=INDEX(Table2[#All],
   MATCH("MRO2",Table2[[#All],[Row Labels]],0),
   MATCH("AMS",Table2[#Headers],0))
Thank you soo much for helping me :)
Related Conversations
Extentions Synchronization
Deleted in Discussions on
3 Replies
Tabs and Dark Mode
cjc2112 in Discussions on
38 Replies
Stable version of Edge insider browser
HotCakeX in Discussions on
35 Replies
Security Community Webinars
Valon_Kolica in Security, Privacy & Compliance on
11 Replies