lookup on 2 rows?

[LiamF]

Hi,

I'm trying to lookup a value within a table but have two rows on which I need to search.

In the attached file I need to, for example, find the Sales for Product B in London, so the value in this example would 4192. The position of Product B within the table can vary although the two cities are always in the same order.

I'm not sure how to do this, any help much appreciated.

Thanks
Liam

[teylyn]

Hi,

this can be done with a combination of an Index/Match and a Vlookup, using a helper table for the vlookup.

see attached.

[LiamF]

excellent. many thanks!

[estige]

I think the MATCH in your solution needs the third argument (type) to be set to 0 to work teylyn?

Please Login or Register  to view this content.

[teylyn]

Good spotting, estige. Guess the data sample was in my favour

time for bed.

The complete formula is

=INDEX($B$2:$B$10,MATCH($E$1,$A$2:$A$10,0)+VLOOKUP($F$1,$I$1:$J$2,2,FALSE))