Use ADDRESS to find column then use that in a formula

[retrorocket129]

Hello. I have the below address formula that finds the column I need in the sheet directly behind this one. Is there a way to use the range info output in the cell in a MATCH formula?

=ADDRESS(3,MATCH("Part Number ",Sheet1!$3:$3,0),,,"Sheet1")&":"&ADDRESS(1048576,MATCH("Part Number ",Sheet1!$3:$3,0))

 

[Anthony47]

Maybe I misinterpreted your question, but you might use INDIRECT(YourFormula)
For Example

=MATCH(TheValue,INDIRECT(ADDRESS(3,MATCH("Part Number ",Sheet1!$3:$3,0),,,"Sheet1")&":"&ADDRESS(1048576,MATCH("Part Number ",Sheet1!$3:$3,0))),False)

I also guess you may replace your INDIRECT + ADDRESS with a simplere OFFSET+MATCH

 

[Anthony47]

I also guess you may replace your INDIRECT + ADDRESS with a simplere OFFSET+MATCH

I mean OFFSET(Sheet1!A3,0,MATCH("Part Number",Sheet1!$3:$3,0)-1,1000000,1) rather than INDIRECT(YourFormula)
So the whole formula would be:

=MATCH(TheValue,OFFSET(Sheet1!A3,0,MATCH("Part Number",Sheet1!$3:$3,0)-1,1000000,1),False)

 

[retrorocket129]

This worked perfect. Thank you!

 

[Anthony47]

Thank you for the feedback