How to convert a simple VLOOKUP to XLOOKUP in Excel?

Question

The VLOOKUP works as intended. The XLOOKUP is giving "#NAME" error:

screenshot of the formula:

the working VLOOKUP:

=VLOOKUP(C12,B24:C29,2,FALSE)

Which I'm reading as "take the value from c12", look at the table B24:C29, lookup the second column.

the not functional XLOOKUP:

=XLOOKUP(C11,B24:B29,C24:C29)

which I'm reading as:

lookup the value in C11, Find the value in the range B24:B29, return the corresponding value in C24:C29.

Possibly I've made a typo, but maybe I'm not understanding the XLOOKUP function sufficiently.

Primarily I'm using Microsoft Co-Pilot and then also reading tutorials.

I'll use the Markdown editor, but a copy paste of data:

Client Name SKU Item Name
McGowan 294211  =XLOOKUP(C11,B24:B29,C24:C29)
Grossman    327064  =VLOOKUP(C12,B24:C29,2,FALSE)
Chamberlain 446741   
Vong    643081   
Williams    294211   
Hicks   446741   
Singh   306862   
Zach    643081   
Li  446741   
Totals       
         
         
SKU Description Price
294211  Leather sectional   1399
306862  Entertainment center    809.99
327064  Media cabinet   379.99
446229  Suede recliner  1099.99
446741  End table set   467.99
643081  Dining table    599.99
         
        
        
        

Answer 1

First things first:

Your VLOOKUP() function shown in OP will certainly return wrong output from 14 th row onwards. The second parameter of VLOOKUP() function is table_array which needs to be either relative absolute reference like B$24:C$29 if not filled right or total absolute reference $B$24:$C$29. That said the formula will be:

---

=VLOOKUP(C11,B$24:C$29,2,0)

---

Secondly,

XLOOKUP() function is available from Excel 2021+ onwards however, I am not sure why Excel_Functions_List_From_MSFT still shows only for MS365, not updated may be. Great resource to learn even if you don't have the said function, if you have then else create an Hotmail/Outlook account and use Excel On Web to learn the usages of the said function. XLOOKUP_with_examples.

---

Thirdly,

Even though its unnecessary, to use an alternative method to VLOOKUP() function, but you can try to use just for learning INDEX()+MATCH() function or VLOOKUP()+CHOOSE() or VLOOKUP()+IF(). Note that the last two combination of formulas are not as efficient like VLOOKUP() and INDEX()+MATCH() functions. That said.

• Using INDEX()+MATCH()

=INDEX(C$24:C$29,MATCH(C11&"",B$24:B$29&"",0))

---

• Using VLOOKUP()+IF() or with CHOOSE()

---

=VLOOKUP(C11,IF({1,0},B$24:B$29,C$24:C$29),2,FALSE)

---

Or,

=VLOOKUP(C11,CHOOSE({1,2},B$24:B$29,C$24:C$29),2,FALSE)

---

Note: Both VLOOKUP() combo functions would need to hit CTRL+SHIFT+ENTER while exiting the edit mode based on ones Excel Versions.

---

An OLD SCHOOL approach, using LOOKUP() function:

=LOOKUP(2,1/(B$24:B$29&""=C11&""),C$24:C$29)

---

---

Just note the above formula works when using the lookup vector is in ascending order, otherwise you may get FALSE POSITIVES. Therefore, the conclusion is its better to use XLOOKUP() (if one have the version of excel) else use VLOOKUP() or INDEX()+MATCH() (as and when required).

---