Possible IF formula confusion

[adarshleo]

Hi everyone,

Can anyone please give the formula that needs to be inputted in cell I16. It is something obviously simple, but i just cant seem to get it. Thanks in advance.

 

[SanjayGMusafir]

Check this and revert -

=XLOOKUP(1,($G$5:G$10=G16)*($H$h:$H$10=H16),$I5:$i10,,0)

 

[adarshleo]

=XLOOKUP(1,($G$5:G$10=G16)*($H$h:$H$10=H16),$I5:$i10,,0)

Sorry its not working.

 

[SanjayGMusafir]

Sorry its not working.

Share XL2BB to check for the possible error.

 

[SanjayGMusafir]

The error seemingly identified. Check this and revert -

=XLOOKUP(1,($G$5:G$10=G16)*($H$5:$H$10=H16),$I5:$I10,,0)

 

[adarshleo]

The error seemingly identified. Check this and revert -

=XLOOKUP(1,($G$5:G$10=G16)*($H$5:$H$10=H16),$I5:$I10,,0)

I had identified this error earlier. It does not work. Here is the XL2BB view:

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)

 

[kevin9999]

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:

=XLOOKUP(1,(TEXT($G$5:G$10,"@")=TEXT(G16,"@"))*(TEXT($H$5:$H$10,"@")=TEXT(H16,"@")),$I$5:$I$10,"No match")

 

[adarshleo]

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:

=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.

 

[kevin9999]

OH YES! You're right, the formulas in H16:H25 were not in numbers. Thanks for pointing that out.

Glad we could help, and thanks for the feedback