• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Return Column Number based ona specific value

D

Dokat

Member
Hi,

How can I return the column number in a range if I know the value and row number?

For Example: My range is

Range: B6:BF59
Value: 12,567
Row# of the Value: 14
What is the formula to get the column number?

I tried below formula but no luck. Formula partially works but returns wrong value when changing selection in a slicer but.

SUMPRODUCT(('Source Data'!$B$7:$BF$59=LOOKUP(2,1/(1-ISBLANK('Source Data'!F7:F59)),'Source Data'!F7:F59))*(COLUMN('Source Data'!$B$7:$BF$59)-1))

Did anyone come across similar issue?
 
Perhaps
=COLUMN(INDEX($B$7:$AA$7,1,MATCH(12567,INDEX($B$7:$BF$598,))
Where 8 is the row number of the value -6 ( when you say row is it the 14th row of the sheet or of the range? I suppose it was the sheet's 14th row)
And the returned column nr is also the sheet's column

Just to be sure, what regional settings are you using , European or Anglo Saxon?
 
Hi Dokat,

If your value and row number are fixed then why whole range?

You can try these if I haven't missed something :eek:

First match:
=MATCH(12567,'Source Data'!$B$14:$BF$14,0)

Last match:
=LOOKUP(2,1/('Source Data'!$B$14:$BF$14=12567),COLUMN($B$14:$BF$14)-1)

Regards,
 
Actually they are not fixed. They are dynamic based on the slicer selection that's why I used lookup formula.

Below formula partially works based on slicer selection however it returns the wrong value in some cases. The value should always be the same regardless of the selection
...not sure what's causing the problem
=SUMPRODUCT(('Source Data'!$B$7:$BF$59=LOOKUP(2,1/(1-ISBLANK('Source Data'!F:F)),'Source Data'!F:F))*(COLUMN('Source Data'!$B$7:$BF$59)))


Formula should return column number 6 but rather returning 12 or 0 based on slicer selection
 
Last edited:
Hi,
Thanks for the clarification.

Upload a sample file, I think Bosco's formula should work, just need to adjust the range.

Regards,
 
See if below works for you:
=MATCH(B4,OFFSET($B$6:$BF$6,B5,0),0)
Where,
B4 = 12567
B5 = Row Number (Relative to start row) so it will be 8 for row 14.
Only hitch is OFFSET is volatile.
 
You must log in or register to reply here.
Back
Top