Using two values for lookup
 

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

Using two values for lookup
 

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

--
Regards

Roger Govier


"Natalie" wrote in message
...
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

Using two values for lookup
 

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))



--
Regards

Roger Govier


"Roger Govier" wrote in message
...
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

--
Regards

Roger Govier


"Natalie" wrote in message
...
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

Using two values for lookup
 

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

Natalie wrote:
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

Using two values for lookup
 

Thank you for this. It worked well.

"Roger Govier" wrote:

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))



--
Regards

Roger Govier


"Roger Govier" wrote in message
...
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

--
Regards

Roger Govier


"Natalie" wrote in message
...
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

Using two values for lookup
 

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.


"Natalie" wrote:

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