Return the value from a third column identified by a first search value in the first column and a second search value in the second column

Question

I would like to display the value of the third column of a table, which is determined by using two search criteria. The table in which is searched consists of three columns.

• The first column contains the names of the players.
• The second column contains the names of the unit.
• The third column contains the power for the players unit.

Example:

Player	Unit	power
Bob	Magmatrooper	19020
Bob	Cad Bane	24250
Bob	TIE Fighter Pilot	22657
Bob	Grand Master Yoda	27979
Bob	Shaak Ti	32988

...

Player	Unit	power
Nik	Magmatrooper	19020
Nik	Cad Bane	21400
Nik	TIE Fighter Pilot	17899
Nik	Grand Master Yoda	23400
Nik	Shaak Ti	29800

My approach: translate an Excel formula to google sheets.

In an Excel spreadsheet, the formula works as shown in Figure 1. Formula, that works in Excel

In google sheets i'm stuck. when the formula is applied it shows an error. Formula in google sheets causes #NV

Can anyone tell what the formula should be in google sheets?

Thanks for your support

Best regards

Hermann

Answer 1

try:

=INDEX(IFNA(VLOOKUP(E2:E3&F1:G1, {A1:A&B1:B, C1:C}, 2, 0)))