Home

Combining ADDRESS with an INDEX MATCH formula, to find cell reference

%3CLINGO-SUB%20id%3D%22lingo-sub-391781%22%20slang%3D%22en-US%22%3ECombining%20ADDRESS%20with%20an%20INDEX%20MATCH%20formula%2C%20to%20find%20cell%20reference%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-391781%22%20slang%3D%22en-US%22%3E%3CP%3EHi%20there%2C%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20currently%20have%20an%20INDEX%20MATCH%20formula%20which%20is%20working%20across%202%20spreadsheets%20and%20returning%20the%20value%20of%20the%20cell%20I%20want%20it%20to%2C%20but%20I%20want%20it%20to%20return%20the%20reference%20of%20the%20cell%20instead%20of%20the%20value%20it%20contains.%26nbsp%3B%20I%20keep%20getting%20different%20errors%20when%20I%20try%20to%20use%20the%20address%20function.%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20think%20I'm%20closer%20to%20using%20it%20correctly%20now%2C%20but%20I'm%20getting%20the%20%22There's%20a%20problem%20with%20the%20formula.%20Not%20trying%20to%20type%20a%20formula%3F%22%20error%20message.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThis%20is%20my%20INDEX%20MATCH%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3DINDEX('%5Botherspreadsheet%5DMay%20'!%24E%2411%3A%24I%2413%2C3%2CMATCH(C3%2C'%5Botherspreadsheet%5DMay%20'!%24E%2411%3A%24I%2411%2C0))%3CBR%20%2F%3E%3CBR%20%2F%3EThis%20works%20and%20is%20currently%20returning%20'E'%20which%20is%20what%20is%20in%20the%20cell%20it's%20found.%20I%20want%20it%20to%20return%20%24G13%20which%20is%20the%20address%20of%20the%20cell%20with%20an%20absolute%20column.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThis%20is%20as%20far%20as%20I've%20got%20putting%20it%20in%20the%20address%20formula%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3DADDRESS(3%2CINDEX('%5Botherspreadsheet%5DMay%20'!%24E%2411%3A%24I%2413%2C3%2CMATCH(C3%2C'%5Botherspreadsheet%5DMay%20'!%24E%2411%3A%24I%2411%2C0))%2C3%2C0%2C'%5Botherspreadsheet%5DMay%20'!)%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI'm%20using%203%20as%20my%20row%20number%20because%20this%20is%20the%20row%20number%20I've%20used%20in%20the%20INDEX%20formula%2C%20so%20it's%20the%20row%20that%20cell%20I%20want%20returning%20is%20in%2C%20but%20I'm%20somewhat%20unsure%20on%20this.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI'm%20not%20sure%20what%20the%20error%20is%20or%20how%20to%20resolve%20it%2C%20or%20even%20if%20ADDRESS%20is%20the%20correct%20formula%20to%20be%20using%20at%20this%20point.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EMany%20thanks%20for%20any%20help%20with%20this!%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-391781%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-909293%22%20slang%3D%22en-US%22%3ERe%3A%20Combining%20ADDRESS%20with%20an%20INDEX%20MATCH%20formula%2C%20to%20find%20cell%20reference%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-909293%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F311738%22%20target%3D%22_blank%22%3E%40asd6231%3C%2FA%3E%26nbsp%3BCan%20you%20please%20give%20me%20the%20actual%20formula%3F%26nbsp%3B%20Thank%20you!%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-392984%22%20slang%3D%22en-US%22%3ERe%3A%20Combining%20ADDRESS%20with%20an%20INDEX%20MATCH%20formula%2C%20to%20find%20cell%20reference%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-392984%22%20slang%3D%22en-US%22%3EI%E2%80%99m%20delighted%20to%20learn%20that.%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-392894%22%20slang%3D%22en-US%22%3ERe%3A%20Combining%20ADDRESS%20with%20an%20INDEX%20MATCH%20formula%2C%20to%20find%20cell%20reference%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-392894%22%20slang%3D%22en-US%22%3E%3CP%3E%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%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EHey%2C%20thank%20you%2C%20this%20worked%20in%20the%20end!%26nbsp%3B%20I%20swear%20I'd%20tried%20using%20CELL%20%22address%22%20before%2C%20but%20it%20did%20finally%20work.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-391897%22%20slang%3D%22en-US%22%3ERe%3A%20Combining%20ADDRESS%20with%20an%20INDEX%20MATCH%20formula%2C%20to%20find%20cell%20reference%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-391897%22%20slang%3D%22en-US%22%3EYou%20may%20construct%20your%20formula%20in%20the%20manner%20described%20in%20this%20link%3A%3CBR%20%2F%3E%3CA%20href%3D%22https%3A%2F%2Fexceljet.net%2Fformula%2Fget-address-of-lookup-result%22%20target%3D%22_blank%22%20rel%3D%22nofollow%20noopener%20noreferrer%20noopener%20noreferrer%22%3Ehttps%3A%2F%2Fexceljet.net%2Fformula%2Fget-address-of-lookup-result%3C%2FA%3E%3C%2FLINGO-BODY%3E
asd6231
New Contributor

Hi there,

 

I currently have an INDEX MATCH formula which is working across 2 spreadsheets and returning the value of the cell I want it to, but I want it to return the reference of the cell instead of the value it contains.  I keep getting different errors when I try to use the address function. 

 

I think I'm closer to using it correctly now, but I'm getting the "There's a problem with the formula. Not trying to type a formula?" error message.

 

This is my INDEX MATCH

 

=INDEX('[otherspreadsheet]May '!$E$11:$I$13,3,MATCH(C3,'[otherspreadsheet]May '!$E$11:$I$11,0))

This works and is currently returning 'E' which is what is in the cell it's found. I want it to return $G13 which is the address of the cell with an absolute column.

 

This is as far as I've got putting it in the address formula

 

=ADDRESS(3,INDEX('[otherspreadsheet]May '!$E$11:$I$13,3,MATCH(C3,'[otherspreadsheet]May '!$E$11:$I$11,0)),3,0,'[otherspreadsheet]May '!)

 

I'm using 3 as my row number because this is the row number I've used in the INDEX formula, so it's the row that cell I want returning is in, but I'm somewhat unsure on this.

 

I'm not sure what the error is or how to resolve it, or even if ADDRESS is the correct formula to be using at this point.

 

Many thanks for any help with this!

 

 

 

 

 

4 Replies
You may construct your formula in the manner described in this link:
https://exceljet.net/formula/get-address-of-lookup-result

@Twifoo 

 

Hey, thank you, this worked in the end!  I swear I'd tried using CELL "address" before, but it did finally work.

I’m delighted to learn that.

@asd6231 Can you please give me the actual formula?  Thank you!

Related Conversations
Tabs and Dark Mode
cjc2112 in Discussions on
16 Replies
Stable version of Edge insider browser
HotCakeX in Discussions on
35 Replies
flashing a white screen while open new tab
cntvertex in Discussions on
11 Replies
How to Prevent Teams from Auto-Launch
chenrylee in Microsoft Teams on
28 Replies