Lookup with THREE conditions: one vertical, two horizontal

Guys, I'm wondering if anyone knows how to do that. I have a table where I need to return a value according to THREE conditions:
1. Vertical, which is a shop code for instance
2. And 2 horizontal:
- One: segment (heading level 1)
- Two: product (heading level 2)

E.g. for "Shop1", what's the value for segment "PI" and product "Credit", etc.
An example file's attached.

The key thing for me is to do this WITHOUT changing data structure, because that really would be a huge pain. I mean, if it's no possible to do without changes (e.g. adding an extra column, etc.), I'll have to live with that I guess. But ideally I would really like to avoid changing data structure.



Thanks so much in advance!

fz73m5zEmZS6Y14-C.xlsx

here you go. index and match result

how it works

Please Login or Register  to view this content.

match finds the specified value in a series and values its location.

match(A11(the shop you want to find),A3:A7(your list of shops),0(shows exact result))

same with your PI or Affluent

index returns the specific value of a cell requested in an array

index(B3:C4(your values),match 1,match 2)

Originally Posted by twiggywales

Attachment 157462

here you go. index and match result

Thanks so much for the reply! However, it actually doesn't quite do what I need it to. It matches by only two conditions (that I managed to do myself as well): one vertical, and one horizontal - two, but not three. What I need it to do is match by THREE. For instance:
- For "Shop1" (vertical)
- For segment "PI" (horizontal)
- For product "Credit" (horizontal)
Two horizontal must match, then matching with vertical.

The reason is simple: I have multiple product values (e.g. many matches for "Credit"), and the only difference is the segment value (e.g. PI, Affluent, Private, etc.). So many segments have the same products in them and those product values for each of the segments are different. That's why I need to match by TWO conditions vertically, not just one.
Have a look at the updated file: if you match by Shop1 vertically and PI horizontally, it will return TWO values (5 and 55) for this lookup. And I need to further narrow it down (and hence choosing the right ONE result from that) by adding an extra horizontal condition (e.g. shop1, pi, credit = 5). Hope it's clear(er) now.

Any help with that?

bit more tricky but this works

Please Login or Register  to view this content.

its an array so needs to be entered with control+shift+enter


fz73m5zEmZS6Y14-C_v2-C.xlsx

Originally Posted by twiggywales

bit more tricky but this works

Please Login or Register  to view this content.

its an array so needs to be entered with control+shift+enter


Attachment 157478

Worked like a charm, and absolutely solved the issue I was having! I really am eternally thankful! Thank you SO, SO much!!!

Np glad I could help