CODEWITHSUNDEEP
excelexcel-formula
Michael Else
Michael Else

Reputation: 13

Search and match using excel headers with duplicated values

As indicated in the example spreadsheet below, I need to identify if a value appears under a specific column header, and there are many repeats of data in the 50 or so columns that this is going to be looking at. I've tried different things but I can't figure out to make excel search the 4 rows beneath where it finds the data to match. I also realize in the example the formula should output a yes or no.

Example spreadsheet:

Example Spreadsheet

Any help would be appreciated. I would like to do this with formulas if possible.

Upvotes: 1

Views: 893

Answers (2)

Glitch_Doctor
Glitch_Doctor

Reputation: 3034

Edited to fit your correct ranges - sheet name needed adding to the first ADDRESS formula

I have completed the formulas up to row 100, you will need to update to the correct number of rows

I came up with this solution, though it uses INDIRECT which is volatile. There may be a better solution (i will try some INDEX MATCHING for it now too) but for now this will work:

=IF(ISERROR(MATCH(C2,INDIRECT(ADDRESS(2,MATCH(F1,'Computed Stat Values'!A1:CB1,0),1,1,"Computed Stat Values")&":"&ADDRESS(100,MATCH(F1,'Computed Stat Values'!A1:CB1,0),1,1)),0)),"No","Yes")

Essentially I am using ADDRESS and INDIRECT to build the array range for the column we want to match in based on the title row match.

ADDRESS(2 and ADDRESS(4 are the start row and end row of match data, please update to cover sufficient rows.

EDIT - Found a much neater way on SO's sister site SuperUser: Link here.

=IF(ISERROR(MATCH(C2,OFFSET('Computed Stat Values'!A1:A100,0,MATCH(F1,'Computed Stat Values'!A1:CB1,0)-1),0)),"No","Yes")

Offset's the column number by the match in the title row. Genius!

Upvotes: 1

Ron Rosenfeld
Ron Rosenfeld

Reputation: 60174

Your worksheet image is in error. Either X and Y should be reversed, or your statement as to what you are looking for.

The following finds out if Y is in the column headed by X

If so, it will return a positive number; if not, it will return an error value.

myTable, X and Y refer to the obvious.

We use the INDEX function with row or column set to 0 to return an entire row or column, depending.

=MATCH(Y,INDEX(myTable,0,MATCH(X,INDEX(myTable,1,0),0)),0)

Upvotes: 1

Related Questions