Using two values for lookup

[Guest]

Hi,

I need to do the following lookup -

Table array

Item Price 1 Price 2 Price 3 Price 4
Vent1 £10.00 £15.00 £20.00 £25.00
Vent2 £12.00 £13.00 £15.00 £16.00
Vent3 £15.00 £20.00 £21.00 £23.00

In main spreadsheet

ColumnA Column B Column E
Vent2 Price 1
Vent1 Price 4
Vent3 Price 2
Vent3 Price 3
Vent2 Price 2

I want it to look at the Price and the item to put the cost in Column E

Thanks

Natalie

 

[Roger Govier]

Hi Natalie

Try
=INDEX(Sheet1!$A$1:$E$4,
MATCH($A2,Sheet1!$A$1:$A$4,0),
MATCH(B$2,Sheet1!$A$1:$E$1,0))

change the Sheet1 reference to whatever your sheet name is but if the
name contains spaces then enclose within single quotes
'Table Array'!$A$1:$E$4

 

[Roger Govier]

sorry typo, $ sign in wrong place for cell B2

=INDEX(Sheet1!$A$1:$E$4,
MATCH($A2,Sheet1!$A$1:$A$4,0),
MATCH(B$2,Sheet1!$A$1:$E$1,0))

should be
=INDEX(Sheet1!$A$1:$E$4,
MATCH($A2,Sheet1!$A$1:$A$4,0),
MATCH($B2,Sheet1!$A$1:$E$1,0))

 

[shail]

Hi Natalie,

You can try this one too.

Suppose you want the results at sheet2 and your data is at sheet1, then

=OFFSET(sheet1!$A$1,MATCH(A10,sheet1!$A$2:$A$4,0),MATCH(B10,sheet1!$B$1:$E$1,0))

Excecute this formula pressing <CTRL><SHIFT><ENTER>

Hope that helps

Thankyou,

Shail

 

[Guest]

Thank you for this. It worked well.

sorry typo, $ sign in wrong place for cell B2


should be
=INDEX(Sheet1!$A$1:$E$4,
MATCH($A2,Sheet1!$A$1:$A$4,0),
MATCH($B2,Sheet1!$A$1:$E$1,0))

 

[Guest]

If you just custom format ColumnA and ColumnB so you just type in the Item
Number and the Price Number, then it is a simple VLOOKUP or HLOOKUP (your
choice). If that isn't clear, I can elaborate.