Jifunze Excel XLOOKUP(): Kutafuta Data Haraka na Rahisi

Gundua jinsi ya kutumia XLOOKUP() kwenye Excel kutafuta data haraka kutoka kwenye rows au columns. Tazama tutorials zaidi, pakua mifumo ya Excel bure, na pata msaada wa WhatsApp.”

1. XLOOKUP() Function

XLOOKUP() ni function ya lookup inayoweza kutafuta thamani kila upande, yaani kutoka column au row, na kurudisha matokeo kwa urahisi. Ni mbadala bora zaidi ya VLOOKUP na HLOOKUP.

Syntax:

XLOOKUP(lookup_value, lookup_array, return_array, [if_not_found], [match_mode], [search_mode])


lookup_value = thamani unayotafuta

lookup_array = range ya data unayotafuta ndani yake

return_array = range ya data unayotaka kurudisha matokeo

if_not_found = thamani ya default ikiwa lookup_value haipo

match_mode = 0 (exact match, default), -1 (exact or next smaller), 1 (exact or next larger), 2 (wildcard match)

search_mode = 1 (first to last, default), -1 (last to first)

2. Mfano Rahisi

Mfano 1: Tafuta jina la mteja kulingana na ID

ID Name
101 John
102 Mary
103 Ali

Formula:

=XLOOKUP(102, A2:A4, B2:B4, "Not Found")


Result: Mary

XLOOKUP inatafuta 102 kwenye column A2:A4 na kurudisha value kutoka B2:B4.

3. XLOOKUP() Badala ya VLOOKUP

Hakuna column index number, unachagua direct return_array.

Inafanya lookup kutoka column yoyote bila kuhitaji kuwa column ya kwanza.

Ina option ya if_not_found badala ya kutumia IFERROR().

Mfano:

=XLOOKUP(105, A2:A4, B2:B4, "Not Found")


Result: Not Found

4. Kutumia XLOOKUP() Pamoja na AND()/OR()

Mfano: Angalia stock ya bidhaa na status

=IF(OR(XLOOKUP(C2, ProductList!A2:A10, ProductList!C2:C10,0)=0, XLOOKUP(C2, ProductList!A2:A10, ProductList!B2:B10,0)="Out of Stock"), "Not Ready", "Ready")


C2 = Product ID

ProductList!A2:A10 = Product IDs, B2:B10 = Status, C2:C10 = Quantity

5. Tips Muhimu

XLOOKUP() ni rahisi, flexible, na inasuluhisha matatizo yote ya VLOOKUP/HLOOKUP.

Inaweza kuunganishwa na IFERROR(), IF(), AND(), OR(), SWITCH(), INDEX(), MATCH() kwa reports changamano.

Inaruhusu lookup kwa rows au columns, na ina default result ikiwa thamani haipo.

Links Muhimu

Tazama tutorials zaidi za Excel: https://www.faulink.com

Download mifumo ya Excel bure: https://www.faulink.com/excel_mifumo.php

Msaada wa WhatsApp: https://wa.me/255693118509