Preskoči na glavno vsebino

Kako vlookup vrednost z več merili v Excelu?

V Excelu lahko hitro preiščemo ustrezne podatke na seznamu na podlagi določenega merila, ki ste ga nastavili s funkcijo vlookup. Ampak, če morate vlookup relativne vrednosti, ki temelji na več merilih, kot je prikazano na spodnji sliki zaslona, ​​kako bi se lahko spoprijeli z njo?

Vlookup vrednost z več merili s funkcijo LOOKUP

Vlookup vrednost z več merili s funkcijo INDEXT in MATCH

Vlookup vrednost z več merili z uporabno funkcijo


Recimo, da imam naslednji obseg podatkov, za katerega želim uporabiti dva merila za vrnitev relativne vrednosti, na primer vem, da morata izdelek in barva vrniti ustreznega prodajalca v isti vrstici:


Vlookup vrednost z več merili s funkcijo LOOKUP

Funkcija LOOKUP vam lahko pomaga rešiti to težavo, vnesite to formulo v določeno celico in pritisnite Vnesite , da dobite pravi rezultat, ki ga želite, si oglejte posnetek zaslona:

=LOOKUP(2,1/($A$2:$A$12=G2)/($C$2:$C$12=H2),($E$2:$E$12))

Opombe: V zgornji formuli:

  • A2: A12 = G2: kar pomeni iskanje meril G2 v območju A2: A12;
  • C2: C12 = H2: pomeni iskanje meril H2 v območju C2: C12;
  • E2: E12: se nanaša na obseg, ki mu želite vrniti ustrezno vrednost.

nasveti: Če imate več kot dva merila, morate le dodati merila v formulo takole: =LOOKUP(2,1/($A$2:$A$12=G2)/($B$2:$B$12=H2)/($C$2:$C$12=I2),($E$2:$E$12)).


Vlookup vrednost z več merili s funkcijo INDEXT in MATCH

V Excelu je mešana funkcija INDEXT in MATCH močna za iskanje vrednosti po enem ali več merilih, če želimo poznati to formulo, naredimo naslednje:

Vnesite spodnjo formulo v prazno celico in pritisnite Ctrl + Shift + Enter tipke skupaj, potem boste dobili relativno vrednost, kot želite, glejte posnetek zaslona:

=INDEX($E$2:$E$12,MATCH(1,($A$2:$A$12=G2)*($C$2:$C$12=H2),0))

Opombe: V zgornji formuli:

  • A2: A12 = G2: kar pomeni iskanje meril G2 v območju A2: A12;
  • C2: C12 = H2: pomeni iskanje meril H2 v območju C2: C12;
  • E2: E12: se nanaša na obseg, ki mu želite vrniti ustrezno vrednost.

nasveti: Če imate več kot dva merila, morate le dodati merila v formulo takole: =INDEX($E$2:$E$12,MATCH(1,($A$2:$A$12=G2)*($B$2:$B$12=H2)*($C$2:$C$12=I2),0)).


Vlookup vrednost z več merili z uporabno funkcijo

Če imate Kutools za Excel, Z njegovim Iskanje v več pogojih lahko hitro vrnete ujemajoče se vrednosti na podlagi več meril, kot jih potrebujete.

Opomba:Če želite uporabiti to Iskanje v več pogojih, najprej bi morali prenesti Kutools za Excelin nato funkcijo hitro in enostavno uporabite.

Po namestitvi Kutools za Excel, naredite tako:

1. Kliknite Kutools > Super POGLED > Iskanje z več pogoji, glej posnetek zaslona:

2. v Iskanje v več pogojih pogovorno okno, naredite naslednje:

  • (1.) V Iskalne vrednosti v razdelku določite obseg iskalne vrednosti ali izberite stolpec iskalne vrednosti enega za drugim, tako da držite Ctrl ključ, na podlagi katerega želite vlookup vrednosti;
  • (2.) V Izhodni razpon v razdelku izberite obseg izhoda, kamor želite postaviti ustrezne rezultate;
  • (3.) V Ključni stolpec v razdelku izberite ustrezne stolpce ključev, ki vsebujejo iskalne vrednosti, eno za drugo, tako da držite tipko Ctrl ključ;
  • Opombe: Število stolpcev, izbranih v Ključni stolpec polje mora biti enako številu stolpcev, izbranih v polju Iskalne vrednosti in vrstni red vsakega izbranega stolpca v Ključni stolpec polje mora ustrezati enemu drugemu s stolpci z merili v Iskalne vrednosti področju.
  • (4.) V Vrni stolpec izberite stolpec, ki vsebuje vrnjene vrednosti, ki jih potrebujete.

3. Nato kliknite OK or Uporabi naenkrat so bile izvlečene vse ujemajoče se vrednosti na podlagi več meril, glejte posnetek zaslona:


Več relativnih člankov:

  • Vrednosti iskanja v več delovnih listih
  • V Excelu lahko enostavno uporabimo funkcijo vlookup za vrnitev ustreznih vrednosti v eni tabeli delovnega lista. Ampak, ali ste že kdaj pomislili, kako vlookup vrednost v več delovnih listov? Recimo, da imam naslednje tri delovne liste z obsegom podatkov, zdaj pa želim dobiti del ustreznih vrednosti na podlagi meril iz teh treh delovnih listov, kako rešiti to opravilo v Excelu?
  • Vlookup vrne prazno ali določeno vrednost namesto 0 ali N / A v Excelu
  • Ko uporabite funkcijo vlookup za vrnitev ustrezne vrednosti, če je vaša ujemajoča se celica prazna, bo vrnila 0, in če vaše ujemajoče se vrednosti ne najdete, boste dobili vrednost napake # N / A, kot je prikazano na sliki spodaj. Namesto da bi prikazali vrednost 0 ali # N / A, kako lahko nastavite, da prikazuje prazno celico ali drugo določeno besedilno vrednost?
  • Vlookup in vrnitev ujemajočih se podatkov med dvema vrednostma
  • V Excelu lahko uporabimo običajno funkcijo Vlookup, da dobimo ustrezno vrednost na podlagi danih podatkov. Toda včasih želimo vlookup in vrniti ujemajočo se vrednost med dvema vrednostma, kot je prikazano na spodnjem posnetku zaslona, ​​kako bi se lahko spoprijeli s to nalogo v Excelu?

Najboljša pisarniška orodja za produktivnost

Priljubljene funkcije: Poiščite, označite ali identificirajte dvojnike   |  Izbriši prazne vrstice   |  Združite stolpce ali celice brez izgube podatkov   |   Krog brez formule ...
Super iskanje: Več kriterijev VLookup    Multiple Value VLookup  |   VLookup na več listih   |   Nejasno iskanje ....
Napredni spustni seznam: Hitro ustvarite spustni seznam   |  Odvisni spustni seznam   |  Večkrat izberite spustni seznam ....
Upravitelj stolpcev: Dodajte določeno število stolpcev  |  Premakni stolpce  |  Preklop stanja vidnosti skritih stolpcev  |  Primerjaj obsege in stolpce ...
Predstavljene funkcije: Mrežni fokus   |  Pogled oblikovanja   |   Velika vrstica formule    Upravitelj delovnih zvezkov in listov   |  Knjižnica virov (Samodejno besedilo)   |  Izbirnik datuma   |  Združite delovne liste   |  Šifriranje/dešifriranje celic    Pošljite e-pošto po seznamu   |  Super filter   |   Poseben filter (filter krepko/ležeče/prečrtano ...) ...
15 najboljših kompletov orodij12 Besedilo Orodja (dodajanje besedila, Odstrani znake,...)   |   50 + Graf Vrste (Gantt Chart,...)   |   40+ Praktično Formule (Izračunajte starost glede na rojstni dan,...)   |   19 vstavljanje Orodja (Vstavite kodo QR, Vstavi sliko s poti,...)   |   12 Pretvorba Orodja (Številke v besede, Pretvorba valut,...)   |   7 Spoji in razdeli Orodja (Napredne kombinirane vrstice, Razdeljene celice,...)   |   ... in več

Napolnite svoje Excelove spretnosti s Kutools za Excel in izkusite učinkovitost kot še nikoli prej. Kutools za Excel ponuja več kot 300 naprednih funkcij za povečanje produktivnosti in prihranek časa.  Kliknite tukaj, če želite pridobiti funkcijo, ki jo najbolj potrebujete...

zavihek kte 201905


Kartica Office prinaša vmesnik z zavihki v Office in poenostavi vaše delo

  • Omogočite urejanje in branje z zavihki v Wordu, Excelu, PowerPointu, Publisher, Access, Visio in Project.
  • Odprite in ustvarite več dokumentov v novih zavihkih istega okna in ne v novih oknih.
  • Poveča vašo produktivnost za 50%in vsak dan zmanjša na stotine klikov miške za vas!
Comments (11)
No ratings yet. Be the first to rate!
This comment was minimized by the moderator on the site
2,1 itu fungsinya apa yah?
This comment was minimized by the moderator on the site
I have sheet where 2 values should be verify from table available in another file in which 2 values from sheet are common and after matching both the criteria e.g Size and type from table it should capture price
This comment was minimized by the moderator on the site
Hello excelmaster,
How are you? You can lookup values in another file. Let me show you two ways. 
Solution 1:
In photo 1,  sheet1 has the original data of the product details. In photo 2, we need to know the price of some items. We can use the help of the new Excel XLOOKUP function to do the trick.The syntax is =XLOOKUP(lookup_value, lookup_array, return_array, [if_not_found], [match_mode], [search_mode]).Omitting the optional arguments, =XLOOKUP(lookup_value, lookup_array, return_array)In cell E2 of sheet2, please input the formula: =XLOOKUP(A2&B2&C2,Sheet1!A2:A12&Sheet1!B2:B12&Sheet1!C2:C12,Sheet1!D2:D12)Then you get the price of the item in E2. To get the rest of the result, we need to keep the arrays in E2 formula absolute.Then the formula becomes:=XLOOKUP(A2&B2&C2,Sheet1!$A$2:$A$12&Sheet1!$B$2:$B$12&Sheet1!$C$2:$C$12,Sheet1!$D$2:$D$12)
Then drag the autofill handle down to get the rest of the results.
Solution 2:Use the Multi-condition Lookup feature in Kutools for Excel. All results will be returned at the same time.Please see photo 3, set the values in Multi-condition Lookup dialog box. Click the OK button to get the results.Please see photo 4, results in sheet2 are returned based on the data in sheet1.
Hope my two solutions can help you. Have a nice day.
Sincerely,Mandy 
This comment was minimized by the moderator on the site
How do i do this
100 100100 ABC100101 DEF101102103 HIJ103
Results i want
100 ABC
100 ABC
100 ABC
100 ABC
101 DEF
101 DEF
102
103 HIJ
103 HIJ

what formula should i be using?
Thanks
This comment was minimized by the moderator on the site
The lookup will not work if there is a formula in the cell, what is the remedy ??
This comment was minimized by the moderator on the site
you are too genius, you solve my issue.
This comment was minimized by the moderator on the site
This is an elegant formula, also easily expansible to more criteria. The one donwside of INDEX+MATCH formulas is that it's really slow in larger datasets.
This comment was minimized by the moderator on the site
Index match should be faster in my personal opinion. It has been tested as well by many. If uses index match in an array, definitely it will be slower since it will become like a volatile formula. The above formula uses index match in array for multiple criteria condition which actually can be change to non-array type as well ;)
This comment was minimized by the moderator on the site
Thanks for this tutorial; :-) I have a question. What formula should I used? I have a series of data in a row like A1:M1, I'd like a result that if there is/are data that is/are < or > in specific number, it will result to "Disqualified" if it's true or " " (space) if false.
This comment was minimized by the moderator on the site
Hello, Thanks for this tutorial, it's very helpful. The following formula works great. =LOOKUP(2,1/(B:B=H97)/(I:I=H98),E:E). I have a simple question. What I want is, the cell should get the value if (H98 = open) If "open" is not there in (I:I) match (H99 = Under observation) from (I:I) and get the value, If possible get the row. I want to keep the formula as lite as possible. As I will be copying this formula in lots of cells. Also kindly suggest which of the above formula (LOOKUP/SUMPRODUCT/INDEX) is less processor intensive.
This comment was minimized by the moderator on the site
=LOOKUP(2,1/(A2:A10=G2)/(B2:B10=G3),(D2:D10)) what does the 2 mean?
There are no comments posted here yet
Please leave your comments in English
Posting as Guest
×
Rate this post:
0   Characters
Suggested Locations