CODEWITHSUNDEEP
excelexcel-formula
Matt Taylor
Matt Taylor

Reputation: 671

Finding value with similar headers in multiple columns

I am trying to do an Offset/Match or Index/Match type formula to find the value that matches a cell. The problem is the way the sheet is laid out.

For an example I am trying to match the Job No. to the Project Name under the appropriate client.

I would like to keep it in this format because the project list will grow.

This sheet is where my lists are kept.
*This is just being created hence why the Project lists are tiny. Lists

This sheet will be an ever expanding list of stuff that needs to be done.
You can see the OFFSET formula below that I tried. Is there another way of doing this or can this be done with a simple formula (instead of a long IF statement for each Client)? To-Do

=OFFSET(D2,MATCH(D3,Lists!F3:P10,0),MATCH(J2,Lists!F3:P3,0))

Thanks for any feedback.

Upvotes: 1

Views: 73

Answers (2)

Tom Sharpe
Tom Sharpe

Reputation: 34180

It seems a bit more awkward to do it with INDEX/MATCH because you have to repeat the column lookup, but here it is

IFERROR(INDEX(INDEX($F$4:$P$10,,MATCH(C3,$F$2:$O$2,0)+1),MATCH(D3,INDEX($F$4:$P$10,,MATCH(C3,$F$2:$O$2,0)),0)),"")

enter image description here

Upvotes: 0

Mabary
Mabary

Reputation: 26

=VLOOKUP(D3,OFFSET(Lists!$E$4:$E$10,,MATCH(C3,Lists!$F$2:$P$2,0),,2),2,0)

Upvotes: 1

Related Questions