Ana içeriğe atla

Bir veya daha fazla kritere göre Vlookup ve birden fazla değer döndürün

Normalde, ilk karşılık gelen değeri almak için Vlookup işlevini kullanabilirsiniz, ancak bazen, eşleşen tüm kayıtları belirli bir kritere göre döndürmek istersiniz. Bu yazıda, tüm eşleşen değerlerin dikey, yatay veya tek bir hücreye nasıl bakılacağı ve döndürüleceği hakkında konuşacağım.

Vlookup ve ilgili tüm değerleri dikey olarak döndür

Vlookup ve karşılık gelen tüm değerleri yatay olarak döndür

Vlookup ve karşılık gelen tüm değerleri bir hücreye döndür

Vlookup ve ilgili tüm değerleri dikey olarak döndür

Eşleşen tüm değerleri belirli bir kritere göre dikey olarak döndürmek için lütfen aşağıdaki dizi formülünü uygulayın:

1. Bu formülü, sonucun çıktısını almak istediğiniz boş bir hücreye girin veya kopyalayın:

=IFERROR(INDEX($C$2:$C$20, SMALL(IF($E$2=$A$2:$A$20, ROW($A$2:$A$20)-ROW($A$2)+1), ROW(1:1))),"" )

not: Yukarıdaki formülde, C2:C20 sütun, döndürmek istediğiniz eşleşen kaydı içerir; A2: A20 sütun ölçütü içeriyorsa; ve E2 değerleri temel alarak döndürmek istediğiniz belirli kriterdir. Lütfen bunları ihtiyacınıza göre değiştirin.

2. Daha sonra, tuşuna basın. Ctrl + Üst Karakter + Enter ilk değeri almak için anahtarları bir araya getirin ve ardından ihtiyaç duyduğunuz tüm ilgili kayıtları almak için doldurma tutamacını aşağı sürükleyin, ekran görüntüsüne bakın:

İpuçları:

Vlookup ve eşleşen tüm değerleri dikey olarak daha spesifik değerlere göre döndürmek için lütfen aşağıdaki formülü uygulayın ve tuşuna basın. Ctrl + Üst Karakter + Enter anahtarlar.

=IFERROR(INDEX($C$2:$C$20, SMALL(IF(1=((--($E$2=$A$2:$A$20))*(--($F$2=$B$2:$B$20))), ROW($A$2:$A$20)-ROW($A$2)+1), ROW(1:1))),"" )

Vlookup ve karşılık gelen tüm değerleri yatay olarak döndür

Yatay sırada görüntülenen eşleşen değerleri elde etmek istiyorsanız, aşağıdaki dizi formülü size yardımcı olabilir.

1. Bu formülü, sonucun çıktısını almak istediğiniz boş bir hücreye girin veya kopyalayın:

=IFERROR(INDEX($C$2:$C$20,SMALL(IF($F$1=$A$2:$A$20,ROW($A$2:$A$20)-ROW($A$2)+1),COLUMN(A1))),"")

not: Yukarıdaki formülde, C2: C20 sütun, döndürmek istediğiniz eşleşen kaydı içerir; A2: A20 sütun ölçütü içeriyorsa; ve F1 değerleri temel alarak döndürmek istediğiniz belirli kriterdir. Lütfen bunları ihtiyacınıza göre değiştirin.

2. Daha sonra, tuşuna basın. Ctrl + Üst Karakter + Enter ilk değeri almak için anahtarları bir araya getirin ve ardından ihtiyacınız olan tüm ilgili kayıtları almak için doldurma tutamacını sağa sürükleyin, ekran görüntüsüne bakın:

İpuçları:

Vlookup ve eşleşen tüm değerleri daha spesifik değerlere göre yatay olarak döndürmek için lütfen aşağıdaki formülü uygulayın ve tuşuna basın. Ctrl + Üst Karakter + Enter anahtarlar.

=IFERROR(INDEX($C$2:$C$20,SMALL(IF(1=((--($F$1=$A$2:$A$20))*(--($F$2=$B$2:$B$20))),ROW($A$2:$A$20)-ROW($A$2)+1),COLUMN(A1))),"")

Vlookup ve karşılık gelen tüm değerleri bir hücreye döndür

Bakmak ve karşılık gelen tüm değerleri tek bir hücreye döndürmek için aşağıdaki dizi formülünü uygulamalısınız.

1. Aşağıdaki formülü boş bir hücreye girin veya kopyalayın:

=TEXTJOIN(", ",TRUE,IF($A$2:$A$20=F1,$C$2:$C$20,""))

not: Yukarıdaki formülde, C2: C20 sütun, döndürmek istediğiniz eşleşen kaydı içerir; A2: A20 sütun ölçütü içeriyorsa; ve F1 değerleri temel alarak döndürmek istediğiniz belirli kriterdir. Lütfen bunları ihtiyacınıza göre değiştirin.

2. Daha sonra, tuşuna basın. Ctrl + Üst Karakter + Enter tüm eşleşen değerleri tek bir hücreye almak için anahtarları bir araya getirin, ekran görüntüsüne bakın:

İpuçları:

Vlookup ve eşleşen tüm değerleri tek bir hücrede daha spesifik değerlere göre döndürmek için lütfen aşağıdaki formülü uygulayın ve tuşuna basın. Ctrl + Üst Karakter + Enter anahtarlar.

=TEXTJOIN(", ",TRUE,IF(($A$2:$A$20=F1)*($B$2:$B$20=F2),$C$2:$C$20,""))

Not: Bu formül yalnızca Excel 2016 ve sonraki sürümlerde başarıyla uygulandı. Excel 2016'nız yoksa lütfen görüntüleyin okuyun aşağı indirmek için.

Daha ilgili Vlookup makaleleri:

  • Vlookup ve Açılır Listeden Birden Çok Değer Döndür
  • Excel'de, bir açılır listeden birden fazla karşılık gelen değeri nasıl görüntüleyebilir ve döndürebilirsiniz; bu, açılır listeden bir öğe seçtiğinizde, aşağıdaki ekran görüntüsü gibi tüm göreli değerleri aynı anda görüntülenir. Bu yazıda çözümü adım adım tanıtacağım.
  • Vlookup Excel'de 0 Veya N / A Yerine Boş Döndürmek İçin
  • Normalde, karşılık gelen değeri döndürmek için vlookup işlevini uyguladığınızda, eşleşen hücreniz boşsa, 0 döndürür ve eşleşen değeriniz bulunmazsa, bir hata #YOK değeri alırsınız. 0 veya # N / A değerini görüntülemek yerine boş hücre göstermesini nasıl sağlayabilirsiniz?
  • Vlookup Excel Tablosundan Birden Çok Sütun Döndürmek İçin
  • Excel çalışma sayfasında, eşleşen değeri bir sütundan döndürmek için Vlookup işlevini uygulayabilirsiniz. Ancak bazen, aşağıda gösterilen ekran görüntüsü gibi, birden çok sütundan eşleşen değerleri çıkarmanız gerekebilir. Vlookup işlevini kullanarak aynı anda birden çok sütundan karşılık gelen değerleri nasıl elde edebilirsiniz?
  • Birden Çok Çalışma Sayfasında Vlookup Değerleri
  • Excel'de, eşleşen değerleri bir çalışma sayfasının tek bir tablosunda döndürmek için vlookup işlevini kolayca uygulayabiliriz. Ancak, birden çok çalışma sayfasında değerin nasıl görüneceğini hiç düşündünüz mü? Veri aralığına sahip aşağıdaki üç çalışma sayfasına sahip olduğumu varsayarsak ve şimdi, bu üç çalışma sayfasından ölçütlere dayalı olarak karşılık gelen değerlerin bir kısmını almak istiyorum.
  • Süper Formül Çubuğu (birden çok metin ve formül satırını kolayca düzenleyin); Okuma Düzeni (çok sayıda hücreyi kolayca okuyun ve düzenleyin); Filtrelenmiş Aralığa Yapıştır...
  • Hücreleri / Satırları / Sütunları Birleştirme ve Verilerin Saklanması; Bölünmüş Hücre İçeriği; Yinelenen Satırları ve Toplam / Ortalamayı Birleştirme... Yinelenen Hücreleri Önleyin; Aralıkları Karşılaştır...
  • Yinelenen veya Benzersiz'i seçin Satırlar; Boş Satırları Seçin (tüm hücreler boştur); Süper Bul ve Bulanık Bul Birçok Çalışma Kitabında; Rastgele Seçim ...
  • Tam kopya Formül referansını değiştirmeden Birden Çok Hücre; Otomatik Referans Oluştur Birden Çok Sayfaya; Madde İşaretleri Ekle, Onay Kutuları ve daha fazlası ...
  • Sık Kullanılan ve Hızlı Eklenen Formüller, Aralıklar, Grafikler ve Resimler; Hücreleri Şifrele şifre ile; Posta Listesi Oluşturun ve e-posta gönder ...
  • Metni Çıkar, Metin Ekle, Konuma Göre Kaldır, Alanı Kaldır; Sayfalama Alt Toplamları Oluşturma ve Yazdırma; Hücre İçeriği ve Yorumları Arasında Dönüştür...
  • Süper Filtre (filtre şemalarını kaydedin ve diğer sayfalara uygulayın); Gelişmiş Sıralama ay / hafta / gün, sıklık ve daha fazlasına göre; Özel Filtre kalın, italik ...
  • Çalışma Kitaplarını ve Çalışma Sayfalarını Birleştirin; Tabloları anahtar sütunlara göre birleştirin; Verileri Birden Çok Sayfaya Bölme; Toplu dönüştürme xls, xlsx ve PDF...
  • Pivot Tablo Gruplaması hafta numarası, haftanın günü ve daha fazlası ... Kilidi Açılmış, Kilitli Hücreleri Göster farklı renklerle; Formülü / Adı Olan Hücreleri Vurgulayın...
kte sekmesi 201905
  • Word, Excel, PowerPoint'te sekmeli düzenlemeyi ve okumayı etkinleştirin, Publisher, Access, Visio ve Project.
  • Yeni pencereler yerine aynı pencerenin yeni sekmelerinde birden çok belge açın ve oluşturun.
  • Üretkenliğinizi% 50 artırır ve her gün sizin için yüzlerce fare tıklamasını azaltır!
ofis tabanı
Comments (8)
No ratings yet. Be the first to rate!
Ahmed Abosamaha
Ahmed Abosamaha
This comment was minimized by the moderator on the site
gents

would you please share with me the formula of the three ways but for data in horizontal table.
Reply
0
0
skyyang
skyyang     Ahmed Abosamaha
This comment was minimized by the moderator on the site
Hello, Ahmed,
To solve your problem, please apply the below formulas:
Get the results vertically: =IFERROR(INDEX($B$2:$K$2, SMALL(IF($A$5=$B$1:$K$1, COLUMN($B$1:$K$1:$K$1)-COLUMN($B$1)+1), ROW(1:1))),"" )
Get the results horizontally: =IFERROR(INDEX($B$2:$K$2, SMALL(IF($D$5=$B$1:$K$1, COLUMN($B$1:$K$1:$K$1)-COLUMN($B$1)+1), COLUMN(A1))),"" )
Ge the results in one cell: =TEXTJOIN(", ",TRUE,IF($B$1:$K$1=J5,$B$2:$K$2,""))
Note: These formulas are array formulas, you should press Ctrl + Shift + Enter keys together to get the correct result.

https://www.extendoffice.com/images/stories/comments/comment-skyyang/doc-vlookup-data.png

Pease try, hope this can help you!
Reply
Report
0
0
Ahmed Abosamaha
Ahmed Abosamaha     skyyang
This comment was minimized by the moderator on the site
thanks for your greet support and quick response
Reply
0
0
itika
itika
This comment was minimized by the moderator on the site
how do u get all rows to fill up like in yr video without pressing ctrl+shift+enter
Reply
0
0
Taia
Taia
This comment was minimized by the moderator on the site
After I extend the data, some of the cells is showing a "0" instead of blank
Reply
0
0
Adam
Adam     Taia
This comment was minimized by the moderator on the site
It means that there is missing data or the wrong type of data in the cell(s) from the column that is being referrenced. For instance, Im working with a bunch of data and I want all ID numbers for workers under a certain supervisor. However, in the table column being referenced with all workers' ID numbers, some cells had the workers last name in it and not their ID numbers. Those exact cells produced 0s when the entire formula was put in, and this is because it was text within a column that is mostly numbers.
Reply
0
0
Win E
Win E
This comment was minimized by the moderator on the site
How can you tweak the formula so that you have multiple outputs in column D? E.g. I want to populate column D with each of the countries, such that all of their respective cities will be returned in E? The only work around I have can see is manually changing $D$2
Reply
0
0
Jeff
Jeff
This comment was minimized by the moderator on the site
Thank you very much, you save lots of work here!





Regarding your formula {=TEXTJOIN(", ",TRUE,IF($A$2:$A$15=D2,$B$2:$B$15,"")},

I changed a little : (=TEXTJOIN(CHAR(10),TRUE,IF($A$2:$A$15=D2,$B$2:$B$15,""))}





Regards,



Jeff
Reply
0
0
There are no comments posted here yet
Please leave your comments in English
Guest
Posting as Guest
×
Rate this post:
Reset
0   Characters
Suggested Locations