Retrieve value based on TWO values in a table in Excel

Question

I have Table2 (on the left) with 3 columns. I want to use them to automatically define values in Table3 (on the right) based on 2 variables in columns A and B.

I want to automatically insert value in column G based on values in columns E and F, so for example if E3 is "Morrisons" and F3 is "x" then following the master table the value in column G should be "1". So the value in column G is based on two variables: column A and column B.

I came with this idea, but it doesn't seem to work. Can you please help? Thank you.

=INDEX(Table2[V];MATCH(Table3[@[Customer]:[index]];Table2[[A]:[B]];0))

also tried this, but didn't work either:

=INDEX(Table2[V];MATCH(AND([@Customer];[@index]);AND(Table2[A];Table2[B]);0))

Thanks, so if I want to get rid of tables which formula can I use to make sure it allows to check for values in two columns? — Piotr Ciszewski
– Piotr Ciszewski, Commented Jan 27, 2020 at 22:34

BigBen · Accepted Answer · 2020-01-27 22:51:33Z

1

Use SUMIFS:

=SUMIFS(Table2[V],Table2[A],[@Customer],Table2[B],[@Index])

Change the , to ; as needed.

answered Jan 27, 2020 at 22:51

BigBen

50.4k7 gold badges29 silver badges45 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Retrieve value based on TWO values in a table in Excel

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related