CODEWITHSUNDEEP
excelexcel-formula
boson
boson

Reputation: 894

Find the cell value that intersects two strings in excel

I'm wondering how I can get excel to find a value based on a string in a column and a string in a row. For example

         a73hfj     a384asd     a848fjs

a          x1          x2          x3

b          y1          y2          y3

c          z1          z2          z3

How can I find y2 by specifying that it is in the row of b and the column of a384asd?

Thanks!

Upvotes: 1

Views: 61

Answers (2)

Faseeh
Faseeh

Reputation: 96

This one also works...

=OFFSET(A1,LOOKUP(C1,B1:D1,ROW(A1:A3)),LOOKUP(C1,A2:A41,ROW(A1:A3)))

Upvotes: 0

Enigmativity
Enigmativity

Reputation: 117144

Try these.

R1C1 mode:

=INDEX(R2C2:R4C4,MATCH("b",R2C1:R4C1,0),MATCH("a384asd",R1C2:R1C4,0))

A1 mode:

=INDEX($B$2:$D$4,MATCH("b",$A$2:$A$4,0),MATCH("a384asd",$B$1:$D$1,0))

Upvotes: 5

Related Questions