=XLOOKUP(1,($G$5:G$10=G16)*($H$h:$H$10=H16),$I5:$i10,,0)
Sorry its not working.=XLOOKUP(1,($G$5:G$10=G16)*($H$h:$H$10=H16),$I5:$i10,,0)
Share XL2BB to check for the possible error.Sorry its not working.
=XLOOKUP(1,($G$5:G$10=G16)*($H$5:$H$10=H16),$I5:$I10,,0)
The error seemingly identified. Check this and revert -
Excel Formula:=XLOOKUP(1,($G$5:G$10=G16)*($H$5:$H$10=H16),$I5:$I10,,0)
Candidate's Assessment Template - Adarsh.xlsx | |||||
---|---|---|---|---|---|
G | H | I | |||
3 | Pre-defined Grade | ||||
4 | Industry Rank | Revenue Rank | Grade as per the industry and revenue rank | ||
5 | 1 | 1 | 0.50 | ||
6 | 1 | 2 | 1.00 | ||
7 | 1 | 3 | 1.50 | ||
8 | 2 | 1 | 2.00 | ||
9 | 2 | 2 | 2.50 | ||
10 | 2 | 3 | 3.00 | ||
11 | |||||
12 | |||||
13 | Question | ||||
14 | |||||
15 | Rank of Industry | Rank in terms of Revenue | Grade | ||
16 | 1 | 2 | #N/A | ||
17 | 2 | 1 | #N/A | ||
18 | 1 | 3 | #N/A | ||
19 | 2 | 2 | #N/A | ||
20 | 1 | 1 | #N/A | ||
21 | 2 | 2 | #N/A | ||
22 | 1 | 3 | #N/A | ||
23 | 1 | 1 | #N/A | ||
24 | 2 | 3 | #N/A | ||
25 | 1 | 2 | #N/A | ||
Question2 - 5 mins (Excel) |
Cell Formulas | ||
---|---|---|
Range | Formula | |
G16:G25 | G16 | =VLOOKUP(B16,$A$5:$B$8,2,0) |
H16:H25 | H16 | =IFS(C16>=2000000,"1",C16>=1000000,"2",C16<1000000,"3") |
I16:I25 | I16 | =XLOOKUP(1,($G$5:G$10=G16)*($H$5:$H$10=H16),$I5:$I10,,0) |
=XLOOKUP(1,(TEXT($G$5:G$10,"@")=TEXT(G16,"@"))*(TEXT($H$5:$H$10,"@")=TEXT(H16,"@")),$I$5:$I$10,"No match")
OH YES! You're right, the formulas in H16:H25 were not in numbers. Thanks for pointing that out.The #N/A error tends to suggest you might be trying to match text values (that look like numbers) with numerical values. Try changing the XLOOKUP formula to this & see if it makes any difference:
Excel Formula:=XLOOKUP(1,(TEXT($G$5:G$10,"@")=TEXT(G16,"@"))*(TEXT($H$5:$H$10,"@")=TEXT(H16,"@")),$I$5:$I$10,"No match")