Forum Discussion
Wrong formula, can't figure out.
So I have this tables for example. In column K i want a formula that compares values from column G and column I to column A and B and finds the right row it's in, and also puts the right value in col...
theboth in cell K8, use the following formula, then copy it down:
=INDEX($C$3:$C$12; MATCH(1; ($A$3:$A$12=G8)*($B$3:$B$12=I8); 0))
Or, if the XLOOKUP function is available in your version of Excel, you could use:
=XLOOKUP(1; ($A$3:$A$12=G8)*($B$3:$B$12=I8); $C$3:$C$12; 0)
Use the optional [if_not_found] parameter of XLOOKUP to return 0 when no match is found. With the INDEX and MATCH method, to return 0 instead of #N/A when no match is found, you would also need to use the IFNA or IFERROR function:
=IFNA(INDEX($C$3:$C$12; MATCH(1; ($A$3:$A$12=G8)*($B$3:$B$12=I8); 0)); 0)
As you can see, XLOOKUP is the preferred method, if it's available to you.
- You are a genius, thank you sooo much.
