Lookup value in a table and return adjacent column value

Hi

I am trying to looup a value in a table and reurn the adjacent matched column value. I have used index and match formula with no success. Please see the attached sample table where I have given the results that hopefully a formula can return. The values in the table can be in any column, e.g. op 50 in column H, E.

Thank you for your help.

I can't really help you with the data as it is laid out. I'm not saying it's impossible, but I'm not going to be trying to do it.

Excel works best with tables of data. So, imagine the data was laid out in a table in columns W, X, Y and Z:

HTML Code: 

The rows with no data could be left blank, if so required.

Then you can set up a table that has two sub-tables: one that totals the qty, and one that totals sub

HTML Code: 

The beauty is that you then only need two fairly straightforward formulae, copied across and down:

In B12: =SUMIFS($Y$3:$Y$20,$W$3:$W$20,$A12,$X$3:$X$20,B$11) and
In I12: =SUMIFS($Z$3:$Z$20,$W$3:$W$20,$A12,$X$3:$X$20,B$11)


That layout makes more sense to me as I feel it is easier to look at the groups rather than alternate columns.

I hope this works for you.


The other option, if you put your data in a table as described, is to use a Pivot Table.


Regards, TMS

Try this in B11, dragged across and down

=IFERROR(IF(B$10<>"sub", INDEX(INDEX($B$3:$S$5,MATCH($A11,$A$3:$A$5,0),), MATCH(B$10, INDEX($B$3:$S$5,MATCH($A11,$A$3:$A$5,0),),0) +1), INDEX(INDEX($B$3:$S$5,MATCH($A11,$A$3:$A$5,0),), MATCH(A$10, INDEX($B$3:$S$5, MATCH($A11,$A$3:$A$5,0),),0)+2)),"")
How's that work for you?

Tough one. I will give it some thought.

Okay, I thought about it.
Assuming that it follows the pattern "material.OPxxx" and that "raw" and "fin" stand alone (or do they have Material. in front of them too?)

Try
=IFERROR(IF(B$10<>"sub", INDEX(INDEX($B$3:$S$5,MATCH($A11,$A$3:$A$5,0),), MATCH(IF(LEFT(B$10,2)="OP",$A11&".","")&B$10, INDEX($B$3:$S$5,MATCH($A11,$A$3:$A$5,0),),0) +1), INDEX(INDEX($B$3:$S$5,MATCH($A11,$A$3:$A$5,0),), MATCH(IF(LEFT(A$10,2)="OP",$A11&".","")&A$10, INDEX($B$3:$S$5, MATCH($A11,$A$3:$A$5,0),),0)+2)),"")
This formula is getting pretty complex.

I overthought. This is simplier

=IFERROR(IF(B$10<>"sub", INDEX(INDEX($B$3:$S$5,MATCH($A11,$A$3:$A$5,0),), MATCH("*"&B$10, INDEX($B$3:$S$5,MATCH($A11,$A$3:$A$5,0),),0) +1), INDEX(INDEX($B$3:$S$5,MATCH($A11,$A$3:$A$5,0),), MATCH("*"&A$10, INDEX($B$3:$S$5, MATCH($A11,$A$3:$A$5,0),),0)+2)),"")

@ChemistB: Even more truly impressive formula. Sadly, no rep for this one

Thanks TM. We'll see if it works. Have a good day.