vlookup/ match or other function??

[Guest]

Hi!

I'm having some difficulties with finding the right formula (probably a
combination of several functions) as I would like to obtain a formula, which
will not only vlookup according to one lookup value (in this case a row
label) but also according to specific column label.
The thing is that I'm looking for some standar formula, which will enable me
to return the correct data, as worksheets differ from time to time - some
have different rows (Peter, Steve, Olga/ the other time Olga, Kate Betty).
The same happens with columns (one time it starts with X, the other time it
starts with Z and does not include P).

Would anyone be able to help me?

I would be glab, as I really can not solve it myself

X Y Z P O
Peter
Caroline 3
Steve
Olga
Kate 4
Betty


Z O Y
Caroline 3 8 5
Kate 4 6 0
Betty 7 2 1

 

[Guest]

One way is to use INDEX/MATCH ..

Assuming source table is in A1:F7, viz:

X Y Z P O
Peter 19 96 11 60 69
Caroline 56 82 32 18 52
Steve 25 68 16 79 69
Olga 36 57 98 25 29
Kate 66 91 93 22 78
Betty 69 43 54 11 13

Then assuming you have the col headers (scrambled)
in I1:M1 across, eg:
Z, O, Y, P, X

with row headers (names, scrambled as well) in H2:H7, eg:
Caroline
Kate
Betty
Steve
Peter
Olga

Put in I2:
=INDEX($A$1:$F$7,MATCH($H2,$A$1:$A$7,0),MATCH(I$1,$A$1:$F$1,0))
Copy I2 across to M2, fill down to M7 to return the required results from
the source table, ie you'd get:

Z O Y P X
Caroline 32 52 82 18 56
Kate 93 78 91 22 66
Betty 54 13 43 11 69
Steve 16 69 68 79 25
Peter 11 69 96 60 19
Olga 98 29 57 25 36

 

[Guest]

Thank you a lot Max!! It really works!

One way is to use INDEX/MATCH ..

Assuming source table is in A1:F7, viz:

X Y Z P O
Peter 19 96 11 60 69
Caroline 56 82 32 18 52
Steve 25 68 16 79 69
Olga 36 57 98 25 29
Kate 66 91 93 22 78
Betty 69 43 54 11 13

Then assuming you have the col headers (scrambled)
in I1:M1 across, eg:
Z, O, Y, P, X

with row headers (names, scrambled as well) in H2:H7, eg:
Caroline
Kate
Betty
Steve
Peter
Olga

Put in I2:
=INDEX($A$1:$F$7,MATCH($H2,$A$1:$A$7,0),MATCH(I$1,$A$1:$F$1,0))
Copy I2 across to M2, fill down to M7 to return the required results from
the source table, ie you'd get:

Z O Y P X
Caroline 32 52 82 18 56
Kate 93 78 91 22 66
Betty 54 13 43 11 69
Steve 16 69 68 79 25
Peter 11 69 96 60 19
Olga 98 29 57 25 36

 

[Max]

Welcome, Claudia. Glad to hear that.