CODEWITHSUNDEEP
excellookup
user2597879
user2597879

Reputation: 97

Two-way lookup function

I have a matrix of values with column headers (ID numbers) up top, and Item numbers down the rows. I want to do a lookup by (ID number, Item number).

Upvotes: 0

Views: 299

Answers (3)

stenci
stenci

Reputation: 8481

This formula:

=INDEX(B2:D4,MATCH(B6,A2:A4,0),MATCH(B7,B1:D1,0))

Works on B8 of this example:

enter image description here

Upvotes: 1

barry houdini
barry houdini

Reputation: 46331

A very common method is to use INDEX with two MATCH functions as described here

INDEX is usually preferable to OFFSET because it isn't volatile (i.e. it doesn't re-calculate evry time the worksheet calculates like OFFSET)

Upvotes: 1

Alin I
Alin I

Reputation: 590

To look in area A1 > E6
G1 is row number, H1 is column number

=VLOOKUP(G1; $A$1:$E$6; MATCH(H1; $A$1:$E$1; 0); FALSE)

or commas "," instead of semicolons ";"

Upvotes: 0

Related Questions