CODEWITHSUNDEEP
excelexcel-formulamatchvlookup
Kangarooo
Kangarooo

Reputation: 228

How in excel to find correct id number from list of different columns to compare to correct cell

I have for different, for example, languages same id and want to Vlookup or index, match or some other way to find correct translation row and to show corresponding id number from that row. I can do with 1 list but heres example with 3 languages.

How to find correct number for translation and getting correct number?

Example

Upvotes: 1

Views: 434

Answers (1)

VBA Pete
VBA Pete

Reputation: 2666

Try the SUMPRODUCT function:

=SUMPRODUCT((G2:I6=C2)*(J2:J6))

enter image description here

Note that SUMPRODUCT function cannot return strings. In order to return strings, I would use the INDEX function:

=INDEX($J$2:$J$6,SUMPRODUCT(($G$2:$I$6=C2)*ROW($J$2:$J$6))-1)

enter image description here

Upvotes: 5

Related Questions