Find a column reference for a cell meeting 2-dimensional criteria

**crazytalker90** · 09-03-2013, 06:10 PM

Hello! I have been trying to do this for many days now, and so finally decided to join this forum and ask for help.

I am trying to get the column number of a cell that meets both vertical and horizontal criteria within a matrix.

Specifically I am trying to produce a formula that will give me the column number of a column that both includes the number 1 and has a specific REF #

Example:

(REF #) (1/4/13) (12/4/12) (7/18/13)
(1) (1) (0) (0)
(2) (0) (0) (1)
(3) (0) (1) (0)

Untitled copy.jpg

The reason I am looking to do this is to produce an OFFSET function that will utilize this column number to give me the heading title (Date) for different REF #s corresponding to where the number 1 is located.

Any tips will help! I've tried using combinations of IF, OR, AND, SUMPRODUCT, VLOOKUP, HLOOKUP, OFFSET, INDEX, COLUMN, and MATCH but can't seem to quite figure it out

~Charlie

**oeldere** · 09-03-2013, 06:13 PM

Without seeing your file, i think the solution will be index/match

**crazytalker90** · 09-03-2013, 06:33 PM

@oeldere, thanks for the heads up, I uploaded a picture of a simplification of my data file, does that help? I want to output the column number where a 1 is listed within the matrix that corresponds to a given REF # (note: REF #s do not necessarily go in order so I need to be able to look up the row based on the REF # and then look up the column based on its matrix value)

~Charlie

**oeldere** · 09-03-2013, 06:56 PM

In a picture we can't work to test the solution.

Please add an excel file, without confidential information (and please add the desired result).

**crazytalker90** · 09-04-2013, 12:13 PM

Okay, thanks for your guidance. Here is a simplified file of what I am trying to accomplish

Thanks!

~Charlie

simplified file.xlsx

**oeldere** · 09-04-2013, 02:58 PM

With the macro below.

It seems a bit of overkill, but I don't have another solution.

See the attached file.

Please Login or Register  to view this content.

**crazytalker90** · 09-04-2013, 04:38 PM

Thanks @oeldere! Do you know if there's a way to accomplish this with formulas alone and not with any macros? I will be sharing this file with others and I would be afraid the macro would be lost in the shuffle

~Charlie

~Charlie

**oeldere** · 09-04-2013, 04:42 PM

I don't used formula's.

Instead I used the code (macro), which you will find in #6.

Please reply, if the result is, as you liked (desired) (expected).

**crazytalker90** · 09-04-2013, 04:45 PM

Thanks @oeldere! Looks great. Also, do you know happend to know if there's a way to accomplish this with formulas alone and not with any macros? I will be sharing this file with others and I would be afraid the macro would be lost in the shuffle

~Charlie

**oeldere** · 09-04-2013, 04:56 PM

Maybe my quote in #6 explain it all.

Please Login or Register  to view this content.

**crazytalker90** · 09-04-2013, 05:15 PM

Ah, gotcha, well thanks for your help @oeldere!

~Charlie

**oeldere** · 09-04-2013, 05:19 PM

Thanks for the reply.

Glad I could help.

**crazytalker90** · 09-05-2013, 05:51 PM

Hey @oeldere! Thought you might be interested, someone in another forum answered my post as well and gave the formula

"Let A:D house the above sample.

Let F2 house 3, a REF # of interest.

G2, just enter:

=MATCH(1,INDEX($B$2:$D$4,MATCH($F2,$A$2:$A$4,0),0),0)

If you want the date in the headers row...

=INDEX($B$1:$D$1,MATCH(1,INDEX($B$2:$D$4,MATCH($F2,$A$2:$A$4,0),0),0))

Duplicate records change the nature of the task (if more than one REF# present in column A):

G2, control+shift+enter, not just enter:
Code:
=MIN(IF(IF($A$2:$A$5=$F2,$B$2:$D$5)=1,$B$1:$D$1))

Thought this may be of interest! All the best

~Charlie

(from http://www.mrexcel.com/forum/excel-q...ml#post3567302 )

**oeldere** · 09-06-2013, 10:28 AM

Thanks for sharing.

Find a column reference for a cell meeting 2-dimensional criteria

LinkBack

Thread Tools

Rate This Thread

Display

Find a column reference for a cell meeting 2-dimensional criteria

Re: Find a column reference for a cell meeting 2-dimensional criteria

Re: Find a column reference for a cell meeting 2-dimensional criteria

Re: Find a column reference for a cell meeting 2-dimensional criteria

Re: Find a column reference for a cell meeting 2-dimensional criteria

Re: Find a column reference for a cell meeting 2-dimensional criteria

Re: Find a column reference for a cell meeting 2-dimensional criteria

Re: Find a column reference for a cell meeting 2-dimensional criteria

Re: Find a column reference for a cell meeting 2-dimensional criteria

Re: Find a column reference for a cell meeting 2-dimensional criteria

Re: Find a column reference for a cell meeting 2-dimensional criteria

Re: Find a column reference for a cell meeting 2-dimensional criteria

Re: Find a column reference for a cell meeting 2-dimensional criteria

Re: Find a column reference for a cell meeting 2-dimensional criteria

Thread Information

Users Browsing this Thread

Similar Threads

[SOLVED] How can a find the row number of a row meeting specified criteria?

Counting unique column entries while also meeting other criteria

Sum one column meeting criteria from two other columns

need to sum third column if meeting 2 different criteria

Use of MATCH and INDEX to find first and last instances meeting criteria

Tags for this Thread

Bookmarks

Bookmarks

Posting Permissions