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 column K. as you can see, it's not doing what it's supposed to do. here is the formula in written form:
=IF(AND(ISNUMBER(MATCH(G10; A5:A14; 0)); ISNUMBER(MATCH(I10; B5:B14; 0))); VLOOKUP(G10; A5:C14; 3; FALSE); "")
What is wrong with it?
4 Replies
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.
- column K i want a formula
I can not figure out wich is column K in your screenshot -:).
If possible,upload your workbook please.- ohh, sorry, I just updated the post with the columns and rows are seen
