Excel - Retrieving a cell based off of multiple criteria

Question

I am attempting to return the value of a cell from within a different sheet based upon two cell values in the second sheet. As an example the Main and Second sheet have three columns each (Item, Code, Number), the difference being that the second sheet has only one row and two drop down lists that are populated with the Item(s) and Code(s) (from the Main sheet - i.e like a database table). From within the Second sheet I would like the Number to be returned from the main sheet, once the Item and Code had been selected from the drop down lists on the Second sheet.

Unfortunately I'm unsure as how to do this based off of multiple criteria. As far as I'm aware VLOOKUP only used one criteria over a range. I've attempted the following below which is accepted by Excel as a valid formula, however it doesn't return the correct value.

{=SUMPRODUCT((Main!A:A='Second'!A7) * (Main!C:C='Second'!E2) * (Main!G:G))}

I've interpreted the above as the first two sets of brackets being the range and condition to match and the last set being the range of values to return from. Is this a correct assumption and is this the correct use of that function?

Are there any other functions I could use?

Thank you, I have very little experience with Excel!

Since you are trying SUMPRODUCT to return a numeric value, it looks like SUMIFS could work for you. — JvdV

Flávio Lopes Flávio Lopes · Accepted Answer · 2020-07-15T14:41:12

Have you taken a look at the magic INDEX combined with MATCH, and perhaps MATCH again?

INDEX returns the contents of a cell from a table provided a row and a column. Usage:

=INDEX([table to look in]; [COLUMN index]; [ROW index])

MATCH returns the indexes that match a criteria in the table. Usage:

=MATCH([Criteria];[table to look in])

And you can nest one inside the other the following way:

=INDEX([table to look for in];MATCH([Criteria];[Table to look for the particular Row]);[COLUMN, or a second MATCH to find the right column])

Or even combine a couple of MATCH as in

=INDEX([table to look for in];AND(MATCH(1);MATCH(2)); [Column to look for, or MATCH to look for in column])

Which I think is what you want.

If you need a SUM of something (as you used a SUM function before), just nest it inside the proper SUM function

Excel - Retrieving a cell based off of multiple criteria

1 Answers