Repeating values in multiple columns

**kollizion** · 06-18-2008, 05:18 PM

I have 2 columns of data, 1 of which is repeating data, and im looking for a formula to give me the ROW # for which the values in 2 cells are TRUE.

Input Cell A1: code001
Input Cell A2: 30BE

Value that i need: the ROW # that contains both of those values. (in this example, i would need it to return ROW 2)

column A column B
code001 15BE
code001 30BE
code001 45BE
code001 60BE
code002 15BE
code002 30BE
code002 45BE
code002 60BE
etc. etc.

Can anyone help? Im thinking its a combination of MATCH, INDEX, VLOOKUP, and/or HLOOKUP??

**martindwilson** · 06-18-2008, 06:19 PM

i'd do it this way putting a helper column in c
then B1 will give row.(but remember if two lots of data are repeated it will only give first row found)

**kollizion** · 06-18-2008, 06:35 PM

Thanks, thats kinda what i did (without the space), just didnt know if there was a way around it.

**martindwilson** · 06-21-2008, 09:16 PM

ok try using either of these array formulas
they need to be entered with ctrl+shift+enter

Please Login or Register  to view this content.

this applies to sheet I have attached it's in C2
or

Please Login or Register  to view this content.

in D2
apparently the second is the one to go with! no idea why

you would have to adjust cell ref as required
note the +3 at end is to return the correct row from sheet as data does not start until row 4 else it would tell you say, martin smith was in row 1

Repeating values in multiple columns

LinkBack

Thread Tools

Rate This Thread

Display

Repeating values in multiple columns

Thread Information

Users Browsing this Thread

Bookmarks

Bookmarks

Posting Permissions