Artikel ini menunjukkan cara mengekstrak record/baris berdasarkan dua ketentuan yang diterapkan ke dua kolom berbeda, Anda dapat dengan mudah memperluas rumus yang ditunjukkan di bawah untuk menyertakan kriteria tambahan
Jika Anda memiliki skenario di mana Anda ingin menerapkan beberapa ketentuan pada satu kolom, baca artikel ini.
Gambar di atas menunjukkan kumpulan data dalam rentang sel B2. D19, nilai sel G3 memungkinkan Anda mencocokkan nilai di kolom B dan sel G4 mencocokkan tanggal di kolom C. Rumus mengembalikan rekaman yang cocok dalam rentang sel F9. H11 ketika kedua kondisi terpenuhi
Saya memiliki tabel 3 kolom (Nama keamanan, tanggal, harga) dan saya harus menemukan harga sekuritas pada tanggal tertentu dalam tabel yang berisi banyak sekuritas dan harga sekuritas ini untuk tanggal yang berbeda
Jika saya bekerja dengan vlookup atau Index-match, saya hanya mendapat harga pertama untuk sekuritas tertentu. Jadi saya tidak dapat menemukan harga sekuritas yang sesuai dengan nama sekuritas dan tanggalnya
=INDEKS($B$3. $D$19, KECIL(JIKA(COUNTIF($G$3, $B$3. $B$19)*COUNTIF($G$4, $C$3. $C$19), COCOK(BARIS($B$3. $D$19),ROW($B$3. $D$19))), BARIS($A$1. A1)), KOLOM($A$1. A1))
Salin sel F9 dan rekatkan ke kanan. Salin sel F9. H9 dan rekatkan sejauh yang diperlukan
Gunakan alat "Evaluate Formula" untuk memeriksa formula dengan sangat detail. Buka tab "Formula", tekan dengan mouse pada tombol "Evaluate Formula".
Kotak dialog terbuka, tekan tombol "Evaluasi" untuk melihat perhitungan rumus langkah demi langkah
Fungsi COUNTIF menghitung jumlah sel yang sama dengan kondisi. Kita dapat menggunakan fungsi COUNTIf untuk mencocokkan kondisi dengan nilai dalam rentang sel B3. B19
Ini mengembalikan array yang berisi nilai sebanyak sel dalam rentang sel B3. B19, nilainya bisa 0 (nol) atau 1. 1 menunjukkan kecocokan, kita dapat menggunakan array nanti untuk mencocokkan nomor baris yang sesuai
Posisi 0 (nol) atau 1 pada array penting, posisinya sesuai dengan posisi pada cell range B3. B19
COUNTIF($G$3, $B$3. $B$19)
COUNTIF("KeamananB", {"KeamananA"; "KeamananB"; "KeamananC"; "KeamananD"; "KeamananA"; "KeamananB"; "KeamananC"; "KeamananD"; "KeamananA"; "KeamananB"; "KeamananC"
Kondisi kedua adalah tanggal yang ditentukan di sel G4, fungsi COUNTIF menghitung kondisi terhadap tanggal di C3. C19
COUNTIF($G$4, $C$3. $C$19)
COUNTIF(39815,{39814; 39815; 39816; 39817; 39814; 39815; 39816; 39817; 39818; 39819; 39820; 39818; 39819; 39820; 39818; 39819; 39820})
Kami membutuhkan kedua kondisi untuk menjadi benar untuk mendapatkan nilai yang benar. Karakter asterisk memungkinkan Anda mengalikan array, hal ini dimungkinkan karena kedua array memiliki ukuran yang sama
COUNTIF($G$3, $B$3. $B$19)*COUNTIF($G$4, $C$3. $C$19)
Fungsi ROW menghitung nomor baris referensi sel. Itu juga bisa mengembalikan larik nomor baris jika referensi adalah rentang sel
BARIS (referensi)
ROW($B$3. $D$19)
Fungsi MATCH mengembalikan posisi relatif item dalam larik atau referensi sel yang cocok dengan nilai tertentu dalam urutan tertentu
COCOK(BARIS($B$3. $D$19),ROW($B$3. $D$19))
COCOK({3; 4; 5; 6; 7; 8; 9; 10; 11; 12; 13; 14; 15; 16; 17; 18; 19}, {3; 4; 5; 6; 7; 8
Larik ini berisi nomor baris yang berisi nomor sebanyak jumlah baris dalam rentang sel $B$3. $D$19
Nilai array 1 diganti dengan nomor baris yang sesuai. 0 (nol) diganti dengan tidak ada yang SALAH
Fungsi IF mengembalikan satu nilai jika uji logika BENAR dan nilai lain jika uji logika SALAH
JIKA(COUNTIF($G$3, $B$3. $B$19)*COUNTIF($G$4, $C$3. $C$19), COCOK(BARIS($B$3. $D$19),ROW($B$3. $D$19)))
JIKA({0; 1; 0; 0; 0; 1; 0; 0; 0; 0; 0; 0; 0; 0; 0; 0; 0}, MATCH(ROW($B$3. $D$19),ROW($B$3. $D$19)))
Fungsi SMALL menampilkan nilai terkecil ke-k dari sekelompok angka. Ini mengabaikan nilai teks dan boolean
KECIL(JIKA(COUNTIF($G$3, $B$3. $B$19)*COUNTIF($G$4, $C$3. $C$19), COCOK(BARIS($B$3. $D$19),ROW($B$3. $D$19))), BARIS($A$1. A1))
KECIL({FALSE; 2; FALSE; FALSE; FALSE; 6; FALSE; FALSE; FALSE; FALSE; FALSE; FALSE; FALSE; FALSE; FALSE; FALSE; FALSE}, ROWS($A$1. A1))
Fungsi ROWS mengembalikan jumlah baris dalam referensi, kita memerlukan fungsi KECIL untuk mengembalikan nomor baris baru di setiap sel, untuk melakukan itu saya menggunakan fungsi ROWS dan referensi yang bertambah saat Anda menyalin rumus dan menempelkannya ke
Referensi $A$1. A1 memiliki dua bagian, bagian absolut $A$1 artinya tidak akan berubah saat rumus disalin ke sel di bawahnya. Bagian kedua adalah referensi relatif A1, yang berubah saat rumus disalin
KECIL({FALSE; 2; FALSE; FALSE; FALSE; 6; FALSE; FALSE; FALSE; FALSE; FALSE; FALSE; FALSE; FALSE; FALSE; FALSE; FALSE}, ROWS($A$1. A1))
KECIL({FALSE; 2; FALSE; FALSE; FALSE; 6; FALSE; FALSE; FALSE; FALSE; FALSE; FALSE; FALSE; FALSE; FALSE; FALSE; FALSE}, 1)
dan mengembalikan 2. 2 adalah angka terkecil dalam larik
Fungsi INDEX mengembalikan nilai dari rentang sel, Anda menentukan nilai berdasarkan nomor baris dan kolom
Fungsi COLUMNS bekerja seperti fungsi ROWS kecuali untuk kolom, lihat penjelasan di atas. Ini memungkinkan kita untuk mengekstrak seluruh baris
INDEX($B$3. $D$19, KECIL(JIKA(COUNTIF($G$3, $B$3. $B$19)*COUNTIF($G$4, $C$3. $C$19), COCOK(BARIS($B$3. $D$19),ROW($B$3. $D$19))), BARIS($A$1. A1)), KOLOM($A$1. A1))
INDEX($B$3. $D$19, 1, 1)
=INDEKS($B$3. $D$19, KECIL(JIKA(COUNTIFS($G$3, $B$3. $B$19, $G$4, $C$3. $C$19), ROW($B$3. $D$19)-MIN(ROW($B$3. $D$19))+1), BARIS(A1)), KOLOM(A1))
Keamanan1. xlsx
=FILTER(B3. D19, (G3=B3. B19)*(G4=C3. C19))
Tanda sama dengan adalah operator logis yang memungkinkan Anda membandingkan nilai dengan nilai. Dalam hal ini, nilai ke beberapa nilai, hasilnya adalah array yang berisi nilai boolean Benar atau Salah
G3=B3. B19
"SecurityB"={"SecurityA"; "SecurityB"; "SecurityC"; "SecurityD"; "SecurityA"; "SecurityB"; "SecurityC"; "SecurityD"; "SecurityA"; "SecurityB"; "SecurityC"; "SecurityD"; "SecurityA"; "SecurityB"; "SecurityC"; "SecurityD"; "SecurityA"}
Kondisi kedua mengidentifikasi sel dalam rentang sel C3. C19 berisi tanggal yang ditentukan dalam sel G4
G4=C3. C19
Kedua kondisi tersebut harus dipenuhi, karakter asterisk memungkinkan Anda untuk mengalikan arti array DAN logika diterapkan
Ketika nilai boolean dikalikan, ekuivalen numeriknya dikembalikan. BENAR = 1 dan SALAH = 0 (nol)
(G3=B3. B19)*(G4=C3. C19)
Fungsi FILTER memungkinkan Anda mengekstrak nilai/baris berdasarkan kondisi atau kriteria. Itu ada dalam kategori Pencarian dan referensi dan hanya tersedia untuk pelanggan Excel 365
FILTER(B3. D19, (G3=B3. B19)*(G4=C3. C19))
FILTER(B3. D19, {0;
{"Keamanan", 45659, 1. 4; . 6}
Keamanan4. xlsx
Gambar di atas menunjukkan filter yang diterapkan pada kumpulan data menggunakan fitur Filter Tingkat Lanjut Excel. Inilah cara membuat filter itu
The image above shows records filtered on items based on condition in B3 and dates based on condition in C3. If both conditions match on the same row the record/row appears in the filtered list
Put the conditions on a row each in order to apply OR-logic instead of AND-logic between conditions, see image below
Lebih dari 300 fungsi Excel dengan informasi mendetail termasuk sintaks, argumen, nilai kembalian, dan contoh untuk sebagian besar fungsi yang digunakan dalam rumus Excel
Tabel Excel menyederhanakan pekerjaan Anda dengan data, menambah atau menghapus data, memfilter, menjumlahkan, menyortir, meningkatkan keterbacaan menggunakan pemformatan sel, referensi sel, rumus, dan lainnya
Memungkinkan Anda memfilter data berdasarkan nilai yang dipilih , teks tertentu, atau kriteria lainnya. Ini juga memungkinkan Anda memfilter data yang ada atau memindahkan nilai yang difilter ke lokasi baru
Memungkinkan Anda mengontrol apa yang dapat diketik pengguna ke dalam sel. Ini memungkinkan Anda menentukan kondisi dan menampilkan pesan khusus jika data yang dimasukkan tidak valid
Memungkinkan pengguna bekerja lebih efisien dengan menampilkan daftar yang nilainya dapat dipilih pengguna. Ini memungkinkan Anda mengontrol apa yang ditampilkan dalam daftar dan lebih cepat daripada mengetik ke dalam sel
Memungkinkan Anda menamai satu sel atau lebih, ini memudahkan untuk menemukan sel menggunakan kotak Nama, membaca dan memahami rumus yang berisi nama, bukan referensi sel
Excel Solver adalah add-in gratis yang menggunakan sel objektif, batasan berdasarkan rumus pada lembar kerja untuk melakukan analisis bagaimana-jika dan masalah keputusan lainnya seperti permutasi dan kombinasi
Memformat sel atau nilai sel berdasarkan kondisi atau kriteria, ada beberapa alat Pemformatan Bersyarat bawaan yang dapat Anda gunakan atau gunakan rumus pemformatan bersyarat yang dibuat khusus
Memungkinkan Anda dengan cepat meringkas sejumlah besar data dengan cara yang sangat ramah pengguna. Fitur Excel yang kuat ini memungkinkan Anda menganalisis, mengatur, dan mengkategorikan data penting secara efisien
VBA adalah singkatan dari Visual Basic for Applications dan merupakan bahasa pemrograman komputer yang dikembangkan oleh Microsoft, memungkinkan Anda untuk mengotomatiskan tugas yang memakan waktu dan membuat fungsi khusus
Program atau subrutin bawaan VBA yang dapat dibuat oleh siapa saja. Gunakan perekam makro untuk membuat makro VBA Anda sendiri dengan cepat
UDF adalah singkatan dari User Defined Functions dan merupakan fungsi yang dibuat khusus yang dapat dibuat oleh siapa saja
chrisham mengatakan.
Oscar, blog Anda benar-benar membuat pengertian Excel saya. Saya telah belajar banyak, terutama dalam penggunaan rumus Array yang kuat ini. terima kasih
Dalam hal ini, hasil Anda akan bagus untuk menghasilkan daftar filter dari kriteria yang disebutkan. Namun jika Anda hanya mencari harga berdasarkan kriteria yang disebutkan, rumus ini akan lebih sederhana
=INDEKS($D$3. $D$19,COCOK($G$3&$G$4,$B$3. $B$19&$C$3. $C$19,0))
chrisham mengatakan.
Tapi saya kira, rumus di atas tidak berfungsi untuk banyak item dengan kriteria yang sama. maaf, saya jauh untuk pergi untuk saya. )
Dave Bonar mengatakan.
Deskripsi masalah (tentu saja disederhanakan)
Saya memiliki daftar karyawan (berdasarkan nomor ID) dan tanggal (berdasarkan thn & mon) saat mereka diberi tugas (tugas) tertentu. Ini ada di buku Kerja, di TAB. Setiap TAB adalah bulan yang terpisah (pertama Jan, 2 Feb, dst. ). Saya memiliki 12 tab (12 lembar kerja) di buku kerja. Setiap TAB, satu bulan, memiliki daftar nomor ID. Beberapa ID mungkin berulang pada lembar kerja yang berbeda, yaitu beberapa mungkin dalam beberapa bulan dan beberapa mungkin hanya dalam dua atau tiga bulan atau hanya satu bulan. Nomor ID hanya akan ditampilkan sekali dalam sebulan untuk satu tugas (duty). Contoh singkatan di bawah ini
Apakah mungkin menggabungkan data, berdasarkan fungsi, atau rumus, atau VBasic) ke lembar kerja ke-13 secara otomatis dan
1. Tampilkan daftar semua nomor ID secara berurutan (tanpa pengulangan)
2. Tampilkan data Jan di kolom B, data Feb di kolom C, dll. , dan beberapa kolom akan kosong karena ID tidak memiliki tugas bulan itu, dan tidak akan ada di lembar kerja untuk bulan itu
Apakah ada rumus, atau fungsi, atau harus dilakukan di Basic?
Saya memiliki buku kerja dengan 12 tab di dalamnya, dan sekarang harus secara manual meletakkan kolom ID berdampingan dan menyalin dan menggeser ke bawah satu sisi di sisi lain agar cocok, dan ulangi proses 12 kali untuk mendapatkan data tahunan di
Mantan
Untuk Januari
Tugas ID
01 C
05 F
09 D
15 X
23 hal
Untuk Feb
Tugas ID
02 M
05 Q
08 A
12 R
20 W
Menyisir Jan dan Feb akan menjadi
Tugas ID
01 C
02 M
05 FQ
08 A
09 D
12 R
15 X
20 W
23 hal
Ini akan diulang setiap bulan untuk membangun semua 12 bulan kolom
Dengan sangat hormat,
Dave Bonar
(504) 697-2395
Ashutosh Dwivedi mengatakan.
Yang terhormat
If you send the excel file, then I'll understand easily and give the better formula. Based on your requirements
Terima kasih
Oscar .
Dave Bonar,
Ya, saya yakin ini bisa otomatis menggunakan vba. Beberapa tindakan yang diperlukan juga dapat diotomatisasi menggunakan rumus excel
Pertanyaan yang sangat menarik. Saya akan mencoba menjawab pertanyaan Anda sesegera mungkin di sini di situs web saya
/ Oscar
Oscar .
Dave Bonar,
Lihat posting ini. https. //www. get-digital-help. com/2010/02/28/combine-data-from-multiple-sheets-in-excel/
/ Oscar
Liudas says.
Hello, Oscar,
First of all id like to thank you for your blog. I have found many very usefull tips and answers, but still i have one problem that i cant solve by my self. So im asking for your help
Here is the problem
i have a data table with 2 columns
A B
2. 93 12. 8
2. 94 12. 2
3 8. 38
3. 03 6. 76
3. 04 5. 33
3. 06 6. 36
Lets say i have a cell with number 3. I need to find a number in column A that has a number >= than 3, but also has the smallest number in column B
(with my cell = 3 it would be 3. 04 from A and 5. 33 from B)
Simple vlookup gives me first >= number, but in most cases in column B is not the smalest number
I hope you can help me,
Best regards,
Liudas
Oscar .
Liudas,
see this post. https. //www. get-digital-help. com/2010/03/24/lookup-using-two-criteria-in-excel/
david says.
hi oscar,
1) am interested to know what is the array formula for only 1 criteria (for example above, Security, only?
2) how to remove/hide the #num. ?
thanks
Oscar .
David,
1) See this post. https. //www. get-digital-help. com/how-to-return-multiple-values-using-vlookup-in-excel/
2) Excel 2007. IFERROR(INDEX(tbl, SMALL(IF(COUNTIF($G$3, $B$3. $B$19)*COUNTIF($G$4, $C$3. $C$19), ROW(tbl)-MIN(ROW(tbl))+1), ROW(A1)), COLUMN(A1));"")
Tom says.
Oscar,
Great Work on this one. This fixed one of my remaining bugs in my spreadsheet. Using the example above, how would you sort the results by 'Price' within the formula?
Thanks
Tom says.
Oscar,
Great Work on this one. This fixed one of my remaining bugs in my spreadsheet. Using the example above, how would you sort the results by 'Price' within the formula?
Thanks
Oscar .
Tom,
Try this array formula in cell F9
=INDEX(tbl, MATCH(SMALL(IF(COUNTIF($G$3, $B$3. $B$19)*COUNTIF($G$4, $C$3. $C$19), $D$3. $D$19), ROW(A1)), $D$3. $D$19, 0), COLUMN(A1)) + CTRL + SHIFT + ENTER
Copy cell F9 and paste it to H9
Copy cell range F9. H9 and paste it down as far as needed
Tom says.
Oscar,
This is close to what I need. In my spreadsheet I do not have the Date to sort by. When I remove the *COUNTIF($G$4,$C$3. $C$19) portion it shows all of the particular Securities. So far so good. Now when I have two securities with the same price on different days it is not soring corectly(notice the date cells in the results). If all of the prices are different it works fine
IFERROR(INDEX(tbl, MATCH(SMALL(IF(COUNTIF($G$3, $B$3. $B$19), $D$3. $D$19), ROW(A1)), $D$3. $D$19, 0), COLUMN(A1)), "")
Thanks
Oscar .
=INDEX(tbl, MATCH(SMALL(IF(COUNTIF($G$3, $B$3. $B$19), COUNTIF($D$3. $D$19, "<"&$D$3. $D$19)+ROW($B$3. $B$19)/1048576), ROW(A1)), COUNTIF($D$3. $D$19, "<"&$D$3. $D$19)+ROW($B$3. $B$19)/1048576, 0), COLUMN(A1))+ CTRL + SHIFT + ENTER
Copy cell F9 and paste it to H9
Copy cell range F9. H9 and paste it down as far as needed
Tom says.
Oscar,
Thanks a bunch. I was able to adapt this to my sheet and got it to work perfectly. Your knowledge is a great asset to others
While I was able to adapt it, I am not quite sure what it was doing. Can you provide some insight on what this bit is doing
COUNTIF($D$3. $D$19, "<"&$D$3. $D$19)+ROW($B$3. $B$19)/1048576)
Again thanks for your help
Tom
Oscar .
Tom,
COUNTIF($D$3. $D$19, "<"&$D$3. $D$19) creates an array containing numbers. The numbers indicate the rank each cell value would have if they were sorted from A to Z. Now if there are two identical cell values the array formula (COUNTIF($D$3. $D$19, "<"&$D$3. $D$19) creates two identical rank numbers. That is why you got the wrong date when you had two identical securities with the same price. To create unique rank numbers I added this to the formula. ROW($B$3. $B$19)/1048576
Tom says.
Luar biasa. Thanks for the explination
Tom says.
Oscar,
I am on to the next part of my project now
Is there a way to combine all of the results into a single cell like with a concatenation with out the formula being extremly large and not containing cells with no values or the seperation characters
In the above array Formula sample Cell H9 would result in
$1,40, $70,60
Saya perlu melakukan seluruh array dan penggabungan dalam satu sel
I have a sample spreadsheet of exactly what I trying to accomplish, but I do not know how to get it to you
Thanks,
Tom
Oscar .
Tom,
Sejauh yang saya tahu, concatenate tidak dapat digunakan dalam rumus array
Membaca tentang. Penggabungan String
Arielle berkata.
Hi Oscar,
Are you able to do this formula but instead of using a specific date, use a greater than date?
Oscar .
Arielle,
=INDEX(tbl, SMALL(IF(COUNTIF($G$3, $B$3. $B$19)*COUNTIF($G$4, "<"&$C$3. $C$19), ROW(tbl)-MIN(ROW(tbl))+1), ROW(A1)), COLUMN(A1)) + CTRL + SHIFT + ENTER copied right as far as needed and then copied down as far as needed
Boris says.
Dear Oscar,
Thank you for this blog. I applied the formula as specified below and it worked well for me
=INDEX(tbl, SMALL(IF(COUNTIF($G$3, $B$3. $B$19)*COUNTIF($G$4, $C$3. $C$19), ROW(tbl)-MIN(ROW(tbl))+1), ROW(A1)), COLUMN(A1))
How would I weak the formula if I want to still match the 2 criterias of your example (i. e. Security and Date) and in addition sort on the price (e. g. increasing prices)?
Thanks,
Boris
Sean says.
Sheet1
A B C D
8 Country Europe
9 Lights 100
10 Type A 200
11
12 Country USA
13 Fuel 40
14 Diesel 200
15
16 Europe Lights Type A 100
17 USA Fuel Diesel 40
Oscar,is there a way to organize this the information into a database format like row 16 onwards,
It picks up all non blanks between the countries putting each line into a separate column
Sean says.
Ignore the numbers after type a and diesel in the first half
Oscar .
Boris,
How would I weak the formula if I want to still match the 2 criterias of your example (i. e. Security and Date) and in addition sort on the price (e. g. increasing prices)?
In your example, I think an array formula would be too complicated. I suggest you use an excel table
Oscar .
Sean,
Formula in cell A19
=INDEX($B$8. $B$14, IF(ROW(A1)=1, 1, ROW(A1)*5-5))
Formula in cell B19
=IF(COLUMN(A1)<3, INDEX($A$9:$B$14, IF(ROW(A1)>1, ROW(A1)*5-5, 1), COLUMN(A1)), INDEX($A$9:$B$14, IF(ROW(A1)>1, ROW(A1)*5-5, 1)+1, COLUMN(A1)-2))
Get the Excel 2007 file *. xlsx
organize-information. xlsx
Sean says.
Oscar,
Thanks. This is very tricky. The row called Country is the dividing line between each section. I am looking to pick up all the non-blank rows between each section. Move everything from column A besides country over to column B. Ignore the amounts in that is in now in column C. My table was slightly wrong. The amount is in the row below country. So the table looks like this
Country USA
Lights 100
Type A
CFL
Country Europe
Diesel 50. 00
Fuel
USA Lights Type A CFL
Europe Diesel Fuel
Sean says.
Is there any to paste screenshots here?
Oscar .
Sean,
If there is not the same number of rows between sections and country is the dividing line, I think vba is the tool for this task
Oscar .
Sean,
Read this post. Excel udf. Reorganize data
Sathish Rosario says.
Dear Mr. Oscar,
Here is my problem
am having col1, col2, col3 and many data below that
now i want to create 3 data validation
Source for the First data validation is all col1
Source for second data validation is col2 which is match with col1
Source for third data validation is col3 which is match with col1 and col2
hope this is clear. please help me
Sathish Rosario says.
how to retrieve a cell value based on other two cell value by using formula (not using VBA)
b e 1
b f 2
d g 1
d h 4
if i enter "b" and "e" means i should get 1
if i enter "b" and "f" means i should get 2
thanks in advance
Muhammad Saleem says.
I a column with over 400 entries. Most of them are 0s. I would like to list the 5 smallest numbers excluding 0s. What is the best possible formula. Thanks
Oscar .
Muhammad Saleem,
read this post. List five smallest numbers, excluding zeros
CC says.
Please can someone help me with this
I need a function (no macros) that will look at D2, go to column B and display everything in column A thats in column B in ascending order by sorting column C. exampl is below
Name group Invested lookup Answer
First Back $5. 00 Kembali Ketiga
Second Back $6. 00 Second
Third Back $7. 00 First
Forth Front $10. 00
Fifth Side $11. 00
Sisi Keenam $12. 00
CC says.
A B C D E
Grup nama Pencarian yang diinvestasikan Jawaban
First Back $5. 00 Kembali Ketiga
Second Back $6. 00 Second
Third Back $7. 00 First
Forth Front $10. 00
Fifth Side $11. 00
Sisi Keenam $12. 00
srikanth .
Hai,
Dalam skenario "Keamanan, Tanggal, Harga" Anda, saya hanya ingin mencocokkan peran Keamanan dan mengembalikan beberapa baris. Saya tidak ingin mencocokkan Date. Tolong bantu
Adam berkata.
Ini sangat berguna. tetapi bagaimana jika saya perlu menambahkan kriteria bersarang tambahan, mis. g. , jika ((A dan B) atau (c))?
Wilayah. Timur Laut, Atlantik Tengah, Tenggara, dll
Nomor. Harus lebih besar dari angka yang ditentukan
Tandai 1. jika berisi 'x', tambahkan ke daftar
Tandai 2. jika mengandung 'x', hapus dari daftar
Saya mencoba menyusun perhitungan array sebagai berikut
IF((COUNTIF($I$7,'Customer Stats'. $C$2. $C$206)*COUNTIF($B$3,"<"&'Statistik Pelanggan'. $D$2. $D$206)) + COUNTIF($B$4, 'Statistik Pelanggan'. $J$2. $J$206))
Di mana $I$7 berisi Wilayah, $B$3 berisi nomor di atas yang harus menjadi catatan untuk memenuhi syarat, dan $B$4 berisi 'x' jika kita ingin mencocokkan catatan yang sesuai di $J$2. $J$206. tapi ternyata saya tidak bisa membuat sarang perhitungan array ini
Jika saya menambahkannya sebagai array * lain, saya bisa membuat catatan yang ditandai muncul dalam daftar, tetapi catatan apa pun yang muncul harus ditandai
Ada ide?
Thanks,
-Adam
Oscar .
Srikanth,
Hai,
Dalam skenario "Keamanan, Tanggal, Harga" Anda, saya hanya ingin mencocokkan peran Keamanan dan mengembalikan beberapa baris. Saya tidak ingin mencocokkan Date. Tolong bantu
Formula ini harus melakukannya
=INDEX(tbl, SMALL(IF(COUNTIF($G$3, $B$3. $B$19), ROW(tbl)-MIN(ROW(tbl))+1), ROW(A1)), COLUMN(A1))
Anda juga bisa menggunakan rumus di posting ini
Cara mengembalikan banyak nilai menggunakan vlookup di excel
Qadeer says.
Luar biasa, tapi saya punya satu pertanyaan?
Rumus di atas hanya cocok dengan peran keamanan dan mengembalikan banyak baris, hasilnya baik-baik saja
yang saya inginkan adalah mengecualikan kolom "keamanan" itu sendiri dalam keluaran
Terima kasih untuk waktu Anda yang berharga
Qadeer
Oscar .
Adam,
Lihat file terlampir
adam. xls
Adam berkata.
Hi Oscar-
Terima kasih untuk sampelnya. sangat dekat, tetapi saya perlu membatasi daftar hanya ke wilayah yang ditentukan - bahkan jika flag 1 cocok. Saya akan memainkannya sedikit dan melihat apa yang bisa saya lakukan, tetapi jika Anda memiliki solusi cepat, beri tahu saya
Terima kasih lagi,
-Adam
Adam berkata.
Mengerti, saya pikir. ini tampaknya berhasil, tetapi masih menguji
=INDEKS($A$5. $C$19, KECIL( JIKA((COUNTIF($A$2,$A$5. $A$19)*($B$5. $B$19>$B$2))+(COUNTIF($A$2,$A$5. $A$19)*ISNUMBER(SEARCH($C$2,$C$5. $C$19))), MATCH(ROW($A$5. $A$19),ROW($A$5. $A$19)), ""), BARIS(A1)), KOLOM(A1))
Oscar .
Adam,
Buka file terlampir
Adam1. xls
Adam berkata.
Juga dekat. tetapi lembar itu membutuhkan bendera yang cocok serta wilayah yang cocok. dalam kode saya di atas, saya pada dasarnya * array, dan kemudian menambahkannya ke array * kedua, yang tampaknya berhasil. Saya juga mengurangi larik *'d ke-3 untuk item daftar yang tidak ingin kami sertakan apa pun yang terjadi (mirip dengan flag1 dalam contoh ini). Only bug seems to be with those list items that have the exclude flag2 set but do not meet the > number requirement, but I can live with that for now. Ini kode terakhir saya (maafkan referensi Nama yang saya tambahkan untuk pemeliharaan di masa mendatang)
=IFERROR(INDEX(AllDataNoHeadings,SMALL(IF((COUNTIF($A$7, AllRegions)*(AllRacks>=$B$3))+(COUNTIF($A$7, AllRegions)*ISNUMBER(SEARCH($N$2,Strategic
Sekali lagi terima kasih untuk situs web yang luar biasa ini. sangat membantu
-Adam
Ray mengatakan.
Rumus ini berfungsi lebih baik daripada yang saya gunakan saat ini, hanya satu kriteria. Namun saya menggunakan rumus di lembar kerja terpisah dengan data saya di lembar yang disebut Data CSAT
The formula I am using, built from the one in the example, referring to the CSAT Data worksheet will result in a n/a message
Bagaimana kita mereferensikan nama pada lembar kerja dalam rumus aslinya?
Any help would be appreciated
Oscar .
Sinar,
Bagaimana kita mereferensikan nama pada lembar kerja dalam rumus aslinya?
Contoh
'Data CSAT'. $A$1
Mike berkata.
Coba sintaks ini (rumus Array jadi ingatlah untuk C. S. E. )
{=MATCH(B57&C57,B2. B51&C2. C51,0)} untuk Excel 2007 atau lebih tinggi
means return the first row number where a value in Col B matches search value in B57 and a a value in Col C matches search value in C57 ON THE SAME ROW
Oscar .
mike,
MATCH(B57&C57,B2. B51&C2. C51,0)} tidak berfungsi jika ada baris duplikat (B2. C51) namun dengan harga yang berbeda. Lihat baris 3 dan 7. PERTANDINGAN(B57&C57,B2. B51&C2. C51,0) hanya mengembalikan nilai pertama
Thanks for commenting
Ramki berkata.
Hai
Blog ini luar biasa
Bagaimana Anda mengembalikan banyak kecocokan dalam contoh di atas, mis. e matching 2 criteria by using "SUMPRODUCT" function?
Gaurav .
Saya mencoba mencocokkan 5 digit angka dengan 10 digit angka dalam dua lembar excel yang berbeda
Misalnya. -
Lembar1. -00085
Lembar2. -9310008522
fungsi apa yang harus saya gunakan untuk mencocokkan nilai-nilai ini karena data sangat besar dan nilai 5 digit juga tidak tetap
Oscar .
Ramki,
Saya tidak tahu caranya
Mengapa Anda ingin menggunakan sumproduct?
Ramki berkata.
Hai. Oscar,
Terima kasih balasannya
Untuk mengembalikan banyak nilai, saya merasa nyaman dengan jumlah penggunaan produk
Tapi saya hanya bisa mendapatkan Nilai Maksimum dari sebuah array dengan jumlah produk
Jika Anda membantu saya mendapatkan semua nilai ganda terkait, saya akan berterima kasih
Oscar .
Gaurav,
Anda dapat menggunakan fungsi pencarian
Dapatkan file contoh
Gaurav. xlsx
Bill Truax .
halo Oscar, saya sedang membuat spreadsheet untuk melacak panggilan untuk pemadam kebakaran lokal saya. saya memiliki kolom "a" sebagai nomor insiden. nomor insiden adalah penggunaan nomor satu kali dalam setahun. kolom "c" adalah nama peralatan dan ada 1 dari 8 kemungkinan nama yang dapat digunakan di sel ini. kolom "h" memiliki rumus untuk memberi saya waktu yang dihabiskan di tempat kejadian. saya membutuhkan bantuan untuk mendapatkan lembar 2 untuk menandai waktu yang dihabiskan untuk panggilan per perangkat. lembar 2 adalah nama-nama personil di TKP. saya ingin mengatur waktu di tempat kejadian sesuai dengan peralatan apa yang mereka gunakan untuk setiap kejadian
contoh
kolom "a" entri terbaru adalah #10
kolom "c" adalah "bt1" atau "bt2" atau "e1" atau "e3" atau "e4" atau "e5" atau "pov" atau "stby"
sering kali akan ada banyak baris dengan kejadian yang sama# di kolom "a" tetapi peralatan berbeda di kolom "c"
Kolom "h" akan memiliki waktu adegan yang dihitung dengan "=f5-d5"(untuk baris itu)
saya perlu memberi tag waktu di layar dari lembar 1 kolom "h" ke kolom nomor insiden yang sesuai "a" sesuai dengan kolom peralatan "c"
Jumlah Terakhir
Enroute Arrival Clear Response Insiden
Insiden # Tanggal Peralatan Waktu Waktu Waktu Waktu Waktu
1 01/03/12 bt2 8. 18 8. 27 18. 45 0. 09. 00 10. 27. 00
2 03/25/12 bt2 8. 20 8. 23 17. 45 0. 03. 00 9. 25. 00
e1 17. 05 17. 10 17. 45 0. 05. 00 0. 40. 00
e3 12. 33 12. 38 17. 45 0. 05. 00 5. 12. 00
3 26/03/12 e4 7. 45 8. 08 10. 22 0. 23. 00 2. 37. 00
4 03/26/12 bt2 11. 14 11. 16 11. 29 0. 02. 00 0. 15. 00
5 27/03/12 pov 13. 10 13. 20 18. 36 0. 10. 00 5. 26. 00
stby 13. 15 13. 20 18. 36 0. 05. 00 5. 21. 00
bt1 13. 15 13. 20 18. 36 0. 05. 00 5. 21. 00
bt2 13. 16 13. 21 18. 36 0. 05. 00 5. 20. 00
6 28/03/12 e1 8. 18 8. 27 18. 45 0. 09. 00 10. 27. 00
e3 8. 20 8. 30 18. 45 0. 10. 00 10. 25. 00
7 28/03/12 bt1 8. 20 8. 23 17. 45 0. 03. 00 9. 25. 00
e5 9. 00 9. 03 17. 45 0. 03. 00 8. 45. 00
8 28/03/12 bt2 9. 20 9. 22 9. 59 0. 02. 00 0. 39. 00
9 29/03/12 e1 17. 45 17. 50 18. 00 0. 05. 00 0. 15. 00
Oscar .
tagihan Truax,
baca posting ini
Melacak panggilan di excel
Ray Campion mengatakan.
Hi Oscar,
Baru saja menemukan blog Anda - sangat berguna, dan saya berniat untuk terjebak di dalamnya. Saya memiliki pertanyaan singkat tentang penggunaan Sum If dan And yang tidak dapat saya temukan di situs Anda. Saya memiliki contoh spreadsheet berikut
Jan Ann 5
Januari Mike 6
Jan Pete 7
Feb Ann 8
Februari Mike 9
Feb 10
Maret Ann 11
Maret Mike 12
Maret Pete 13
Saya mencari fungsi di mana saya bisa mencari Feb, dan Mike dan mengembalikan angka 9
Saya merasa ini agak rendah untuk situs Anda - tetapi saya terjebak di dalamnya - maaf
Thanks
sinar
Ray Campion mengatakan.
Demi kejelasan - seharusnya ditampilkan sebagai kolom terpisah - jadi bulan ada di kolom A, Nama di Kolom B, dan angka di kolom C
Ray Campion mengatakan.
Menemukan solusinya di dalam blog - menggunakan SUMIFS - tks untuk kontennya
Lukas .
Hi Oscar,
Saya telah melihat contoh Anda hari ini dan menggaruk-garuk kepala tentang mengapa itu tidak berfungsi di versi excel saya. Akhirnya, saya sampai pada kesimpulan bahwa ini pasti ada hubungannya dengan fakta bahwa saya melihat buku kerja Anda di Excel Versi Bahasa Jerman saya
Entah bagaimana rumusnya, terutama KECIL, sepertinya tidak memperbarui referensi relatifnya ketika saya melakukan Array
Hanya demi mengetahui. Anda tidak melakukan apa pun selain CTRL+ALT+RETURN saat membuat Array, bukan?
Bersulang,
Lukas
Lukas .
Hi Oscar,
Maaf untuk posting sebelumnya. Jangan ragu untuk menghapusnya. Saya baru saja mengetahui bahwa saya hanya perlu membuat array dalam satu sel dan kemudian menyalinnya
Terima kasih atas artikelnya. Hal yang sangat menarik
Lukas
Muhammad Nadeem Bhatti mengatakan.
Hi Oscar,
Bagaimana cara menampilkan hasil rumus array pada lembar kerja baru di file yang sama?
Muhammad Nadeem Bhatti mengatakan.
Hi Oscar,
Mohon abaikan postingan saya sebelumnya. Saya ingin bertanya tentang dua kemungkinan tentang rumus array yang dijelaskan untuk Keamanan. file xlsx
1) Bagaimana menampilkan hasilnya pada lembar kerja baru di file yang sama?
2) Saya ingin mencari catatan untuk bulan Januari di file contoh Anda?
Saya ingin tahu solusi secepatnya
Nantikan bantuan Anda dalam masalah ini
Bersulang,
M. Nadeem Bhatti
kata Beth .
Hi Oscar,
Adakah kemungkinan ini dapat dimodifikasi untuk menghasilkan daftar yang dihasilkan di banyak tab, 23 baris per tab?
Jadi rumus di tab kedua akan mengabaikan 23 hasil pencocokan pertama, dan rumus di tab ketiga akan mengabaikan 46 hasil pertama, dst.
Thanks,
kata Beth .
menggunakan array, yaitu
Tabel Pivot dan makro bukanlah opsi untuk buku kerja khusus ini
thanks
Tony berkata.
Hai,
Saya memiliki model yang memiliki tiga buku kerja. Buku kerja 1 dan 2 berisi kumpulan data yang berbeda – hanya ada dua kolom di lembar kerja 1 yang dapat dicocokkan dengan lembar kerja 2. Lembar kerja 3 adalah halaman ringkasan saya tempat saya menampilkan hasil
Saya memerlukan data untuk diuji untuk kondisi – yaitu, saya hanya tertarik pada satu baris di Lembar Kerja 2 yang memiliki dua kolom spesifik yang cocok dengan dua kolom tertentu dalam satu baris di Lembar Kerja 1. Saya ingin melihat sel tertentu dari setiap baris ini yang kondisinya telah terbukti
Masalah ini mirip dengan yang diposting pada awal utas ini, namun saya memerlukan fleksibilitas yang lebih besar dalam kriteria. Pada dasarnya saya meminta setiap baris dalam lembar kerja 2 untuk dipindai terhadap lembar kerja 1 dan, ketika kriteria di atas terpenuhi, tampilkan sel dari baris yang cocok itu
Silakan lihat di bawah untuk sebagian data. Imagine that each of the tables are in different worksheets
Saya ingin sel-sel tertentu di lembar kerja 2 dari baris mana. ‘tenant name’ in worksheet 2 matches ‘building/tenant’ in worksheet 1 and where ‘premises’ in worksheet 2 matches ‘suite id’ in worksheet 1. Curlington Legal Consulting is an example of such a match
This is required as i need data from one worksheet, however the other contains critical search data
I also require an additional screening - that is, i do not require any data from worksheet 1 (even if both previous fields have been met) if the expiry is greater than six months away
Can anyone propose some formulas that could retrieve what i need?
All help is greatly appreciated. It has many people at my firm stumped
Oscar .
Tony,
I am not sure I am following but I gave it a try
Tony. xlsx
Oscar .
Beth,
Two or three tabs maximum using array formulas, I think
Vlookup across multiple sheets in excel
Oscar .
Muhammad Nadeem Bhatti,
1) Bagaimana menampilkan hasilnya pada lembar kerja baru di file yang sama?
Adjust cell references. Example
=INDEX(tbl, SMALL(IF(COUNTIF($G$3, Sheet1. $B$3. $B$19)*COUNTIF($G$4, Sheet1. $C$3. $C$19), ROW(tbl)-MIN(ROW(tbl))+1), ROW(A1)), COLUMN(A1))
2) Saya ingin mencari catatan untuk bulan Januari di file contoh Anda?
=INDEX(tbl, SMALL(IF(COUNTIF($G$3, Sheet1. $B$3. $B$19)*MONTH(Sheet1. $C$3. $C$19)=1, ROW(tbl)-MIN(ROW(tbl))+1), ROW(A1)), COLUMN(A1))
Tony berkata.
Thanks Oscar,
how do i upload a file to show you specifically what i mean?
Tieku says.
Hello Oscar,
I want to search a first vertical range, and then a second vertial range, and then referce the cell in the third column for which the range searches are true
e. g. Saya mencari rentang A1. A10 looking for the value 1, and find it in A1 and A2. Now I want to narrow this down by searching range B1. B10 (I am actually searching B1. B2 now) to find a second value and find it in B2. So the value I want to refence in C2. How do I achieve this. It is similar to your first example except in my case, AX and BX will always be unique and I want to reference cell CX
Tieku says.
Where do I put "tbl (B3. D19)" to define the range of my table?
Tony berkata.
Hi Oscar, thanks for your help
As i cannot upload your file with changes, i will try to describe what i mean
I guess it's important to say that you were so close - the structure of your worksheets was spot on
Please note, however, that the matching data will not be in the same rows on each worksheet. In your initial attempt, all matching ‘tenant name’ and premises’ are in the same rows of each data worksheet. Everything else you assumed, however, is perfect
It is also important to note that where there has been a match found in worksheet 1 and 2 for ‘tenant name’ and ‘premises’, this will be unique. In such an event, I would like other data returned from worksheet one, like what has been done in your first model
As stated previously, i really want a solution that essentially scans each row individually over every row in worksheet 2, and where a match found, other data from worksheet 1 is displayed in worksheet 3. It would be a good if there was a controlled error message (i. e. how you can display desired text in '=if=' functions) where data is not found, so that i can then sort the results page by a particular metric, unless of course you have a more elegant solution
Also, as I am a naive excel user, why does this type of syntax require “Ctrl + Alt + Enter” to perform its function. Why won’t it work without it? What is so different in function about an ‘array’ formula?
Thanks again Oscar
Oscar .
Hi Tony,
I´ll answer your questons as soon as I can. You can use this this contact form to upload your file. Contact form
Oscar .
Tony,
Take a look at this file. Tony_v2. xlsx
Oscar .
Tieku,
Array formula
=INDEX($D$3. $D$19, SMALL(IF(COUNTIF($G$3, $B$3. $B$19)*COUNTIF($G$4, $C$3. $C$19), ROW(tbl)-MIN(ROW(tbl))+1), ROW(A1)), COLUMN(A1))
The formula returns values from cell range $D$3. $D$19 where $G$3 = $B$3. $B$19 and $G$4 = $C$3. $C$19
Karen G says.
I need to return a value from nine columns based on the following criteria
Matching an Origin Zip Code (one column)
Matching a Destination Zip Code (one column)
Weight of a shipment (values are split out in nine columns based on weight ranges. 0-500, 501-1000, 1001-2000, etc
To further complicate this, I need to calculate in a 40% discount on the rate returned (weight column) and add a 17% fuel service charge
TOLONG. LOL
Oscar .
Karen G,
Interesting question, can you provide some example data and the desired outcome?
Karen G says.
Good morning. I'd be happy to. I work in supply chain logistics and am putting together a decision tool to determine the best way to ship goods. We can ship either Full Truck (FT) or Less Than Truckload (LTL). I have a LTL Tariff Schedule which shows the cost of shipping between two different zip codes, based on various weight ranges. The value is 'pennies per 100 weight', and is the first step in calculating the cost for shipping the product. After the weight 'range' is determined, I have to deduct our average discount (40%) and add back the estimated fuel surcharges (17. 5%)
I have the calculation part of this done. =(F13923/100)*(500/100)*0. 6*1. 175
What I'm struggling with is creating a formula to match the two zip codes, and find the correct 'weight' from the column ranges
Mungkin lebih mudah jika Anda membuka Excel dan memasukkan informasi ini
COLUMN HEADERS
A. Origin Zip
Asal st
Zip tujuan
St
0-500
501-1000
1001-2000
2001-5000
5001-10000
ROW DATA
1566 MA 500 NY55 4629 3575 2723 2406 1728
Karen G says.
COLUMN HEADERS
A. Origin Zip
B. Origin St
C. Dest Zip
D. Dest St
E. 0-500
F. 501-1000
G. 1001-2000
H. 2001-5000
I. 5001-10000
ROW DATA
A. 1566
B. MA
C. 500
D. NY55
E. 4629
F. 3575
G. 2723
H. 2406
I. 1728
Karen G says.
The result of this Tariff Schedule needs to be pulled into another document, similar to VLOOKUP. I need to use the Origin Zip (our distribution center), the Destination Zip (customer's distribution center) and weight of shipment to calculate the correct cost of each shipment
COLUMN A COLUMN B COLUMN C COLUMN D COLUMN E
Customer Customer DC Origin Zip Dest Zip Weight
CVS Bessemer, AL 35023 46158 35023 1005
CVS Conroe, TX 77385 46158 77385 500
COLUMN F = Returns Cost of Shipment from Tariff Schedule outlined in previous post above
I hope this makes sense
Doug Bold says.
Hai Semua - Saya ingin mencari 2 kriteria dalam daftar data yang disimpan pada lembar kerja kedua dan jika 2 kriteria tersebut terpenuhi, kembalikan hasil yang disimpan di sel ke-3 pada lembar kerja sekunder - mungkin ada beberapa hasil yang benar dan . I've been all around the answer and had almost the right answer but sadly now my brain has exploded with frustration. Any help may result in the provision of beer or chocolate
Oscar .
Karen G,
See attached file
KarenG. xlsx
Karen G says.
Thank you so much. We're almost there. The calculation is off by one cell/line. Your formula returned 2723 between zip codes 46158 and 35023; however, the real number should be 1826. Your formula returned the weight for 2000 pounds between the first set of zip codes (01566 and 00500)
Origin Zip Dest Zip Weight Cost of Shipment
46158 35023 1005 2723 (your formula return)
Origin Zip Origin St Dest Zip Dest St 500 1000 2000
1566 MA 500 NY55 4629 3575 2723
46158 35023 AL 2025 1826 7864
How can the numbers in Row 1 Cells E-I act as the decider for the actual weight?
Karen G says.
Greetings. We have figured it out and I wanted to share what we did with you. Hopefully this can help someone else too
We concatenated both Zip Codes to create one unique primary key. 46158 - 35000
Then created a unique VLOOKUP chart in addition to the LTL Tariff Schedule
Weight Value Columns
500 2
1000 3
2000 4
5000 5
10000 6
20000 7
30000 8
40000 9
The following VLOOKUP formula was created using the zip code primary key and actual weight of the shipment
=VLOOKUP(I2,'LTL Tariff Schedule'. $E$2. $M$77025,'LTL Calculation'. J2,FALSE)
WORKS LIKE A CHARM
Thank you so very much for taking time to try to help me. I greatly appreciate it
Oscar .
Karen G,
I think I forgot to tell you the formulas in the attached file are array formulas
I hope this picture explains why the formula returned 7864 for cost of shipment
Origin Zip Dest Zip Weight Cost of Shipment
46158 35023 1005 2723 (your formula return)
Also, see the weight ranges in row 6
S. Babu says.
Dear Oscar,
I m working on the below table
ORDER MODEL MATERIAL QTY STATUS
BOM a s6 1 COMPLETED
BOM b c6 2 NOT COMPLETED
BOM c s6 1 COMPLETED
DEL d c6 3 NOT COMPLETED
EXP a a8 4 IN PROGRESS
DEL b d2 5 COMPLETED
DEL c c6 4 NOT COMPLETED
DEL d s6 7 NOT COMPLETED
DEL e c6 8 TIDAK SELESAI
DEL r a8 1 COMPLETED
EXP g d1 5 COMPLETED
EXP r c6 9 COMPLETED
EXP t a8 2 COMPLETED
EXP a c6 1 NOT COMPLETED
EXP b s6 9 COMPLETED
EXP c c6 1 NOT COMPLETED
EXP d a8 4 NOT COMPLETED
I need the status column to be vlooked up on another file by comparing all the remaining 4 columns. (the sheet to be updated carries the 4 columns not in the same order as in the original sheet. its mixed). Pls help me with dis
Thanks
S. Babu
Karen G says.
Try concatenating several fields together to make one (unique) primary key. Contoh nomor PO dan nomor pelanggan
Oscar .
S. Babu,
baca posting ini
Lookup multiple values in different columns and return a single value
Mohsin Ali Raziq says.
I have problem, and o dont know how to solve it, i have data of almost 10000 forms, from which i have to fine 1500, so it is very difficult to dig out 1500 one by one through Ctrl+F, is there any way to to put all the forms number at once and then find all those just by pressing. the prinary key is Form Number
S. Zeb says.
Hi Oscar
I have a table on one tab with a list of actions and when they are due to start and when they have been completed with a description. I am creating a cover page on the first tab to show just the last 7 days action descriptions and the next 7 days planned action descriptions for a management summary
I can't seem to work out how I return all the Action descriptions from today and the last 7 days for completed and tomorrow and the next 7 for planned. Can an Array do this?
Quickly search a table using many criteria . Get Digital Help - Microsoft Excel resource says.
[. ] Records, Search/Lookup, Sort values on Sep. 21, 2012. Email This article to a Friend Mohsin Ali Raziq asks. I have problem, and o dont know how to solve it, i have data of almost 10000 forms, from which i [. ]
Andrew says.
Hi Oscar,
I'm designing a fairly simple sheet to calculate a 'border' around a product . There are 3 different materials that can be used, each with a different price per metre. On the sheet, I have a box for user input as to which frame they'd like to use
I'm now stuck trying to work out which function will allow the sheet to recognise which size material has been used (I used a drop-down box so the user can make a choice from 3 options) and then calculate the total cost from the option they choose. I have all the values in place (options 1,2,3 and cost per ,metre) but I don't which function to use in the calculation box
This is probably a really simple operation but I've never had to calculate something like this
Thanks for any help you can offer
Amanda says.
Awesome post, thank you so much this is exactly what I needed
ROW function explained . Get Digital Help - Microsoft Excel resource says.
[. ] Match two criteria and return multiple rows in excel [. ]
ahmed says.
Hello Oscar,
I know very little about excel. and seeking your expert advice regarding the below scenerio
I got 2 spread sheets
1st one with 50000 rows and columns upto CJ
2nd one with 3000 rows and same number of columns
I need to extract rows from the 1st spreadsheet using values from column CI from the 2nd spreadsheet
values are not numericals. How can I do it?
Oscar .
ahmed,
Are you comparing values in column CI in both sheets?
Perhaps you can use the method described in this post
Quickly compare two tables in excel 2007
Use countif instead of countifs
Sudhakar says.
Dear Oscar,
Can you help me to Retrieve a row details based on three conditions, out of that two will have in the same column and the third one on another, thanks in advance. Regards;
Sudhakar says.
Dear Oscar,
I myself modified your formula to suit my requirements of 3 conditions, however kindly advise my if sorting can be done based on Ascending
Contoh
=INDEX(tbl,SMALL(IF(OR(COUNTIF($C$12,$C$40. $C$84),COUNTIF($C$13,$C$40. $C$84))*COUNTIF($D$19,$D$40. $D$84),ROW(tbl)-MIN(ROW(tbl))+1),ROW(A1)),COLUMN(A1))
Regards
Sudhakar
Miguel Venegas says.
Hello Oscar,
First off, Thanks for all the great help you do on your website. This website has helped me countless times
Here is my questions. It's similar to this post
I wrote a vba macro that searches a column in a data range for a specific number. Ketika menemukan baris yang cocok dengan nomor itu, baris itu akan disalin dan ditempelkan ke tab lain, membuat daftar. Tujuan saya adalah untuk memungkinkan orang membuat perubahan pada baris data tersebut (pada tab baru) lalu setelah selesai, perbarui baris spesifik tersebut ke rentang data asli melalui "tombol kirim". The problem i'm facing is that when they decide they want to update these rows, i need update the rows that match two specific columns. I'm aware of how to do the replacing of the row when I find the rows that match, but i'm unaware of how to find those rows and have it return to me the row number i need to replace
Example of main data range
Proyek Proyek# Jam Karyawan
BestBuy 54511 Miguel 20
BestBuy 54511 Martha 10
BestBuy 54511 John 40
WalMart 14513 Yohanes 30
WalMart 14513 Martha 10
WalMart 14513 Miguel 70
Example of results when they search for a specific Project#
Proyek Proyek# Jam Karyawan
BestBuy 54511 Miguel 20
BestBuy 54511 Martha 10
BestBuy 54511 John 40
Example of changes they wish to submit back into master (AT THIS POINT IS WHERE I NEED TO FIND THE ROW NUMBER OF ROWS THAT MATCH PROJECT# & EMPLOYEE)
Proyek Proyek# Jam Karyawan
BestBuy 54511 Miguel 70
BestBuy 54511 Martha 0
BestBuy 54511 John 30
Example of UPDATED main data range
Proyek Proyek# Jam Karyawan
BestBuy 54511 Miguel 70
BestBuy 54511 Martha 0
BestBuy 54511 John 30
WalMart 14513 Yohanes 30
WalMart 14513 Martha 10
WalMart 14513 Miguel 70
Hope you can help me out, Let me know if you have any other questions or need me to clarify something
Patrick Wolf says.
Hi Oscar,
the definition is. COUNTIF(range,criteria)
but from your formula it looks like you are using
COUNTIF(criteria,range)
ie above. COUNTIF($G$3, $B$3. $B$19)
What you are doing on your awesome blog posts does work so I was just wondering if you could shed more light on it?
Thanks
Patrick
Siddharth says.
hello Oscar sir,
i want your argent help
i want to create 1 formula
ex
if i get value in one cell 2 then it will ans 3
i get 4 or 5 it will return 6
if i get value 7 or 8 then it will return 9
so it will possible then plz reply fast
gilgamesh says.
thank you so much, it solves my problem beautifully
Oscar .
gilgamesh,
Thank you
Rashid says.
Hi Oscar,
Thanks very much for this informative guide. I found it very helpful, and although I just need a bit of help with a certain problem
I used your array formula with great success to find the search results from multiple critera. However my problem is modifying your formula. In the above example you have shown us, you have two criteria. And you distinguish the two criteria by using *
My question is. apa yang akan anda lakukan jika anda tidak mengetahui jumlah kriteria yang telah ditentukan sebelumnya. So lets say the person searching only specifies security and not date. Or only date, and not security. Or maybe both. The problem is you don't know from before hand
How would you go about solving this probem?
Your help is greatly appreciated, thanks so much
Oscar .
Rashid,
Great question. This post explains to some degree what you are asking for
Lookup using multiple conditions
Rashid says.
Thanks for the quick response Oscar,you have greatly helped me many times over
I will take a look at the link provided, thanks very much
Oscar .
Rashid,
baca posting ini
Lookup with an unknown number of criteria
https. //www. get-digital-help. com/2013/10/18/lookup-with-an-unknown-number-of-criteria/
Jon E says.
I was wondering if you can recreate this in Numbers on iPad. I am trying to create a cross platform version, but it says the formulas are not compatible. Can somebody help me with this? Not just this article, but everything dealing with popup menus as well
Oscar .
Jon E,
I have no clue
says
[…] lookup with multiple criteria. Indeed, I think the tutorial in the following link is useful. Match two criteria and return multiple rows in excel . Get Digital Help - Microsoft Excel resource (Using the sample from the above link) BUT sometimes in my case, the Cell G3 and G4 may contain a […]
Monzer George Yazigi says.
Dear Oscar,
Let me start by saying thank you for your help
I have a challenge in which I have been trying to fix for long, but never managed to make it work. Hope you can help
Excel. 2011 MAC
Info
Three columns A, B and C of Table "TBL"
Search Criteria. $D$11
Goal
To load rows from column B into data validation list based on Column A matching $D$11
Can this be done?
Regards
Oscar .
Monzer George Yazigi,
Yes, it can be done. But you need a helper column
Extract-values-and-use-them-in-a-drop-down-list-data-validation. xlsx
Monzer George Yazigi says.
Thanks for your kind help
Highly appreciated
Adam berkata.
Hi Oscar,
I have an issue with a payroll schedule
The payroll schedule has a number of columns. Employee Code, Pay Code, Hours and Rate. Each employee code could have from 1 to 5 different Pay codes. I want to lookup the employee code, if found then look for pay code number 1, if found; return the code, hours and rate, if not found; return blank. Next row, (same employee); look for paycode number 2 etc to paycode 5. Then next employee etc
Terima kasih
Dale Utterson says.
Hi Oscar,
Hai,
Saya memiliki spreadsheet data sepak bola yang mencantumkan semua pertandingan sejak 2000/01 (14 musim). Setiap baris (pertandingan) menampilkan tanggal, tim tuan rumah, gol tim tuan rumah, gol tim tandang, tim tandang & hasil - H, D atau A. Columns are A, B, C, D, E, & F
What I would like to do is for each occurrence of home team & away team to add the result to the Head2Head field (Column G). So if the two teams have played each other for the last 2 seasons and the home team won both, the Head2Head cell would show HH. Similarly if they have played for 6 seasons it could display something like HHDADH. If they have played more than 6 seasons it should drop the earliest to show the latest 6. The earliest result would be on the left of the cell and the most recent on the right. In the last example if a new game produced a draw HHDADH would become HDADHD
I am not an excel expert so I'm finding it quite difficult to adapt some of your suggestions on the blog
Hope I have explained it clearly enough
Thanks in advance
says
[…] table when referencing. This should work for you. --------------------------------- Check out Match two criteria and return multiple rows in excel . Get Digital Help - Microsoft Excel resource for more information on how this array formula […]
Iain says.
Hi Oscar,
I am trying to use similar formulas to these to produce a to do list. I deal with FOI requests, and they get reminders sent out at 10, 15, 17 and 19 days, and are due for response on the 20 day (or earlier)
I want to produce a list on the first tab, looking up info from the log (2nd tab), where a request is due it's equivalent reminder and hasn't already had it, or is due for response that day and hasn't yet been closed
Tab 2 "RequestLog" - Column B is the reference number - this is what i want in the to-do list
Columns V - Y have a P (tick in wingdings2) if their reminder has been sent
Column AA - Has the closed status of the request (is blank if no response has been sent)
Column AX - Has the days remaining until deadline
On tab 1 i have a column for each of the reminders, i. e. for 10 days I want the reference to show up when AX is 5, V (the 10 day column) is blank and AA is blank. This would be the same for all reminders, just changing the "between" part to relate to the days (i. e. 15 days. 3, 17 days. 1, 19 days. =1)
For the 10 day reminder list, I have used the following formula, which will give me the first instance, but when i copy it down, it just says the same again & again
=INDEX(RequestLog. $A$8. $AX$1008,MATCH(1,(RequestLog. $AA$8. $AA$1008="")*(RequestLog. $V$8. $V$1008="")*(RequestLog. $AX$8. $AX$1008=5),0),2)
I think i am just missing one tiny piece that will allow it to give what i need. Can you help (and is this enough info)?
Preferably, i would want this to hide errors (N/A's), but i can live with having them there
Thanks in advance for anything light you can shed
Iain says.
Sorry, have just re-read my post and realised i pasted completely the wrong formula. this is what i have so far managed to get to show the first instance of a FOI requiring a 10 days reminder
=INDEX(RequestLog. $A$8. $AX$1008,MATCH(1,(RequestLog. $AA$8. $AA$1008="")*(RequestLog. $V$8. $V$1008="")*(RequestLog. $AX$8. $AX$10085),0),2)
Untuk kolom pengingat lainnya, yang perlu saya ubah hanyalah angka setelahnya
I hope this all makes sense
Iain says.
Still the wrong one. 3rd time lucky
=INDEX(RequestLog. $A$8. $AX$1008,MATCH(1,(RequestLog. $AA$8. $AA$1008="")*(RequestLog. $V$8. $V$1008="")*(RequestLog. $AX$8. $AX$10085),0),2)
Iain says.
Ok, this website does something to my formula once i press "add comment. Apologies for the multiple comments/posts
Basically, for the column AX there should be two brackets, one less than 11 (looking at the above the website doesn't post that symbol) and the other greater than 5
Will says.
My question is more about the quantity of criteria, I have a table with a number of types, product, ref numbers etc. and I want to create a separate sheet that has a look up function so I can find a single item that matches 3 different criteria (there are 100's of items some with very minor differences). I know I can do this with MATCH and INDEX but I want to know if there is a way to find the same solution by only entering 2 or the criteria (i. e. all three criteria are available to fill but I only need to enter two for the solution to return
Thanks in advance
Darrell says.
Hello. I found your article great, as it was just what I needed to figure out. Unfortunately I couldn't get it to work. . ( Am I being dim, but the CountIf(criteria, range) just counts the number of instances of of that criteria within the range, does it not, so when I use your splitting up example of (COUNTIF($G$3, $B$3. $B$19)
menjadi
COUNTIF("SecurityB", {SecurityA, SecurityB, SecurityC, SecurityD, SecurityA, SecurityB, SecurityC, SecurityD, SecurityA, SecurityB, SecurityC, SecurityD, SecurityA, SecurityB, SecurityC, SecurityD, SecurityA})
and reurns this array. {0, 1, 0, 0, 0, 1, 0, 0, 0, 1, 0, 0, 0, 1, 0, 0, 0}
just returns '4' for me
I am not used to working in arrays yet, but I hope to learn. Thank you in anticipation
Kurt says.
This is fantastic. I would like the table to be longer, say through B3. D100. I changed the tbl to include this and the formulas in the countifs to include 100, but it is not coming up with the right information in the search results. Can you help?
Thanks
suresh says.
PLS HELP ME TO SOLVE THE BELOW
I NEED TO SEARCH TWO WORKBOOKS, IF A OF 1ST WORKBOOK MATCHES WITH B OF 2ND WORKBOOK THEN IF THE QTY OF C IN 2ND WORKBOOK IS GREATER THAN 1, RETRIVE A RESULT AS 1 OR ELSE 0
Alexei says.
Hi Oscar,
Your blog is fantastic. It makes so many things about excel much clearer. I have used this array formula successfully to get information based on 2 criteria and return the outupt in columns, except it does not work past the first row
{=INDEX(Table24, SMALL(IF(COUNTIFS($A3, customerID, J$1, companyList), ROW(Table24)-MIN(ROW(Table24))), ROW(I2)), 23)}
I removed the +1 you had inside the small because it was returning the next row down for some reason but even when I add it back in the formula does not work past the first row
I read through all the comments for this thread and your thread on VLOOKUP returning multiple values which seemed to address this problem but none of the suggested tweaks seem to work for me
Please help
Prabin says.
Good evening Oscar
Your invaluable tips have been helpful for many enthusiastic excel users like me
Could you please shed light on a scenario. I want to create an excel sheet "B" which feeds itself from data in sheet "A"(which we don't have to play with). The crux is sheet "A" gets periodically updated and has fixed number of rows; and sheet "B" needs to be auto-updated in its consecutive rows, with it looking up value in sheet 'A" and pasting only new data from sheet 'A". This means number of rows of sheet "B" will be constantly increasing. Can we use some formula that can update sheet "B" with new value from sheet "A" every time we drag down formula in sheet "B"?
Terima kasih banyak,
Prabin
Oscar .
Prabin
with it looking up value in sheet 'A" and pasting only new data from sheet 'A". Ini berarti jumlah baris lembar "B" akan terus bertambah
If I understand you correctly, you can't save old data to a sheet, using a formula. You need a macro to do that
Prabin says.
Thank you for your kind attention Oscar. Any suggestion on what type of Macro could achieve that?
Regards,
Prabin
Kobi S. says.
Hi Oscar,
Great Blog
I discovered that the formula does not work if you are looking for the same value in different columns
What I mean is, if I have a table and the same values in two different columns and I would like to return all rows that have the needed value in one of the column the formula will not return anything
If I change it to be a different value everything works
Do you happen to know how this could be fixed?
Thanks
Oscar .
Kobi S
Here is the original formula
=INDEX(tbl, SMALL(IF(COUNTIF($G$3, $B$3. $B$19)*COUNTIF($G$4, $C$3. $C$19), ROW(tbl)-MIN(ROW(tbl))+1), ROW(A1)), COLUMN(A1))
If you want to look for the same value in two different columns, use this formula
=INDEX(tbl, SMALL(IF(COUNTIF($G$3, $B$3. $B$19)*COUNTIF($G$3, $C$3. $C$19), ROW(tbl)-MIN(ROW(tbl))+1), ROW(A1)), COLUMN(A1))
It looks for value in cell G3 in both cell range B3. B19 and C3. C19, if both cells match on the same row the entire record is extracted
Kobi S. says.
Hi Oscar,
I need to implement a logical OR meaning
(0,0) = Do not report
(0,1), (1,0) & (1,1) = Report line
I tried changing the "*" between the countif to "+" or using countifs but it did not work
Any thoughts?
Oscar .
Kobi S
You are right, use a + sign like this
=INDEX(tbl, SMALL(IF(COUNTIF($G$3, $B$3. $B$19)+COUNTIF($G$3, $C$3. $C$19), ROW(tbl)-MIN(ROW(tbl))+1), ROW(A1)), COLUMN(A1))
Remember it is an array formula
Kobi S. says.
Worked like a charm
Thank you
Shafiq says.
Hi
Thank you very much
It's working
satish says.
the same security data , what will be the formula if i want , the records which belong to security B and price greater than $ 40. 0
Graciela says.
Hi, could you please help me, i need to find a formula that extract and list the different numbers that match with 2 conditions
To find the work number that match with the date and the area. Thank you,
Oscar .
Graciela
If security is your area condition and date is your date condition and your work numbers are in column D (price in example at the very top of this article)
Array formula
=INDEX($D$3. $D$19, SMALL(IF(COUNTIF($G$3, $B$3. $B$19)*COUNTIF($G$4, $C$3. $C$19), ROW($B$3. $D$19)-MIN(ROW($B$3. $D$19))+1), ROW(A1)))
Stefan berkata.
Hi Oscar,
great page but I still need help
I have two tables and I have to find all Connectors/Cross sections combinations in Table 1 and write the terminal number in Table 2 column E
Table 2 Column C "First match"
{=INDEX($A$3. $A$7;MATCH(1;(FIND(","&A12&",";$B$3. $B$7)>0)*(FIND(","&B12&",";$C$3. $C$7)>0);0))}
Table 2 Column D Count
{=SUMPRODUCT(ISNUMBER((FIND(","&A12&",";$B$3. $B$7))*(FIND(","&B12&",";$C$3. $C$7)))*TRUE)}
My problem is to join all found results/terminals together in one cell
Do you have any suggestion for me?
A B C D E
1 Table 1
2 Terminal Connectors Cross sections
3 Nr1 ,S1,S2,S3, ,0. 25,0. 75,1. 0,
4 Nr2 ,S4,S5,S3, ,0. 25,1. 0,
5 Nr3 ,S6,S7, ,0. 35,0. 5,
6 Nr4 ,S4,S8,S9,S10, ,0. 5,1. 0,
7 Nr5 ,S1,S3, ,0. 25,0. 75,
8
9
10 Table 2
11 Connector Cross section first match count Expected
12 S1 0. 75 Nr1 2 Nr1,Nr5
13 S2 0. 5 #N/A 0
14 S3 0. 25 Nr1 3 Nr1,Nr2,Nr5
15 S4 1. 0 Nr2 2 Nr2,Nr4
16 S5 0. 25 Nr2 1 Nr2
17 S6 0. 75 #N/A 0
18 S7 0. 5 Nr3 1 Nr3
19 S8 0. 5 Nr4 1 Nr4
20 S9 0. 5 Nr4 1 Nr4
21 S10 1. 0 Nr4 1 Nr4
22 S1 0. 25 Nr1 2 Nr1,Nr5
Thanks in advance
Stefan
Stefan berkata.
Hello Oscar,
thanks for the links
My problem now is that I do not get the result matrix before joining
{IF({1;0;0;0;1};$A$3. $A$7;"")}
should return
{Nr1;"";"";"";Nr5}
But it returns only "Nr1"
What I´m doing wrong?
Thanks again for you help
Stefan berkata.
Hi Oscar,
I´ve got it know. . -)
Thanks for your help
Stefan
Oscar .
The answer to this is entering the formula as an array formula, if anyone else has this question
To enter an array formula, type the formula in a cell then press and hold CTRL + SHIFT simultaneously, now press Enter once. Release all keys
The formula bar now shows the formula with a beginning and ending curly bracket telling you that you entered the formula successfully. Don't enter the curly brackets yourself
Winnie Low says.
Hi Oscar,
Thanks for writing this guide. Can I know for the security file what if I want to change the criteria to return results for Security B with prices less than $65? How can I change the formula in cell H9? I've tried changing countif part of it to "<65" but it doesn't seem to work. Thank you
Oscar .
Winnie Low
try this
=INDEX(tbl,SMALL(IF(COUNTIF($G$3,$B$3. $B$19)*COUNTIF($G$4,$C$3. $C$19)*($D$3. $D$19<65),ROW(tbl)-MIN(ROW(tbl))+1),ROW(A1)),COLUMN(A1))
Max says.
Hi Oscar,
Thank you for the detailed explanation, however, I'm still struggling with applying this formula to the following situation
Tab 1
Col A Col B Col C
20 A Jake
20 A Jake
20 A Jim
20 A Jake
20 A Mike
20 B Jake
30 A Mike
30 C John
30 C Mike
Tab 2
Col A Cell B1. A / B / C
20
20
20
20
30
30
30
In tab 2, I need to return the name from column C when col A & B Match the values of col A & cell B1. Namun, jika saya mengembalikan nama depan, saya ingin nilai pengembalian kedua menjadi nama BERBEDA BERIKUTNYA. Do you have any idea how to assist? It would be amazing if you could help, since I've been struggling with this for a while
Thank you in advance. Kind regards, Max
Adi says.
Hi Oscar,
I have a basic question as I am not able to wrap my head around COUNTIF($G$3, $B$3. $B$19)
Does the range not come up first followed by the criteria? I made a mini table to understand but do not get why we switch the range and the criteria. Please let me know
Adi
Oscar .
Hi Adi,
Yes, the range first and then the criteria
=COUNTIF (range, criteria)
The COUNTIF function returns an array if you use multiple criteria provided you enter it as an array formula. The array contains the same number of values as there are criteria
Check out this article for examples
https. //www. get-digital-help. com/2013/03/27/countif-function/
Hannah says.
Hello Oscar,
I have adapted your formula to a case of my own but for some reason is not counting the first entry in each of my scenarios in which I am using it. Say I should have 8 results. It skips the first and only shows me the next 7
Oscar .
Hanna,
Make sure the cell reference in the ROW function points to cell A1 on the first result. The formula will change when you copy the first row of cells and paste them to rows below
=INDEX($B$3. $D$19, SMALL(IF(COUNTIF($G$3, $B$3. $B$19)*COUNTIF($G$4, $C$3. $C$19), ROW($B$3. $D$19)-MIN(ROW($B$3. $D$19))+1), ROW(A1)), COLUMN(A1))
A better formula is
=INDEX($B$3. $D$19, SMALL(IF(COUNTIF($G$3, $B$3. $B$19)*COUNTIF($G$4, $C$3. $C$19), ROW($B$3. $D$19)-MIN(ROW($B$3. $D$19))+1), ROWS($A$1. A1)), COLUMNS($A$1. A1))
This formula won't break if you insert rows or columns on the same worksheet
Desmond LEE says.
Hi Oscar
what about below. Is there any similar post I can try out?
Criteria
First these columns has to be exact matched -(($C$2. $C$12=O2)*($D$2. $D$12=P2)*($E$2. $E$12=Q2)*($F$2. $F$12=R2)*($G$2. $G$12=S2),$B$2. $B$12),1))
Second criteria Column N & B2. B12 . The exact match value the date in column N must not overlap after in B2. B12 in the matched value. See the example above that I have manual enter the map ID
Index match value at A column (Running ID number 0001,0002,0003. ) kembalikan nilai A ke kolom M jika kecocokan di atas cocok
Cassie Spradlin says.
Hai,
Thank you for your video, it has been extremely helpful. I've gotten to the SMALL function piece and I have 3 criteria I'm trying to match on. Saya memiliki 5 baris yang harus memiliki data dan mengembalikan nomor baris di bagian pernyataan IF. Apakah saya melakukan sesuatu yang salah?
=KECIL(JIKA(COUNTIF(Ringkasan. $C$1,'Alasan Batal'. $B$3. $B$1173)*COUNTIF(Ringkasan. $C$2,'Alasan Batal'. $D$3. $D$1173)*COUNTIF(Ringkasan. $C$3,'Alasan Batal'. $C$3. $C1173),MATCH(ROW($B$3. $B$1173),ROW($B$3. $B$1173)),""),ROWS($A$1. A1))
KD mengatakan.
Pemahaman terbatas dan saya telah berjuang untuk mengembalikan daftar berdasarkan beberapa kriteria tetapi, berkat halaman ini, saya rasa saya membuat kemajuan
Saya mencoba membuat daftar berdasarkan apakah tanggal yang ditentukan (di sel H8) antara tanggal mulai dan tanggal akhir untuk baris pada lembar terpisah (Input Tanggal). The formula below works but it's based on matching the start dates and matching the end dates. How would I change this to = end dates . I think I'm getting confused 'cos a countif would usually be range first then criteria?
=IFERROR(INDEX('Data Input'. $A$39. $H$536,SMALL(IF(COUNTIF(Sheet1. H$8,'Data Input'. $G$39. $G$536)*COUNTIF(Sheet1. H$8,'Data Input'. $H$39. $H$536),ROW('Data Input'. $A$39. $H$536)-MIN(ROW('Data Input'. $A$39. $H$536))+1),ROW('Data Input'. A6. D6)),COLUMN('Data Input'. A6. D6)),"")
Stu says.
Hey Oscar
Fantastic tip thank you. Saya telah berhasil mengintegrasikan dengan opsi yang lebih besar dari, yang berfungsi, tetapi saya bertanya-tanya apakah mungkin untuk mengurutkan ulang baris hasil berdasarkan nilai numerik dari satu bidang
e. g. Cari
kriteria 1
hasil
hasil 1 = 8
hasil 2 = 12
hasil 3 = 5
hasil 4 = 22
hasil 5 = 2
Saya ingin itu terjadi
hasil 5 = 2
hasil 3 = 5
hasil 1 = 8
hasil 2 = 12
hasil 4 = 22
RAKESH KUMAR SHARMA mengatakan.
Yang terhormat,
Tolong bantu saya, saya butuh rumus. jika data saya ada di Kolom yang ada di sel berbeda. dan nilai data yang sama ada di sisi bawah kolom di baris yang berbeda. yang juga berada di sel yang berbeda
saya perlu jumlah nilai yang tiga sel data dalam sel
Misalnya
KOLOM DATA 1 2 3 16 18 20
JUMLAH NILAI 50 500 120 240 240 240
NILAI JUMLAH DATA KOLOM
1-2-3 670
2-3-16 860
18-3-20 600
Yogesh Khandait mengatakan.
=COUNTIFS(Daftar. J5. J440, 'Pergeseran Kekuatan Bijaksana'. A26, Daftar. K5. K440, 'Pergeseran Kekuatan Bijaksana'. B26, Kehadiran. S5. S440,"P")
Dapatkah seseorang membantu saya dengan ini?
mengapa saya tidak bisa mendapatkan hitungan pada sheet3 dari nilai dari sheet 2 sambil mencocokkan kriteria dari sheet 1 & 2 keduanya
Josie berkata.
Dapatkah seseorang membantu saya?
Saya mencoba menambahkan kriteria pencocokan tambahan ke formula saya
=IFERROR(INDEX('Lembar 1'. C. C,COCOK(BARIS($2. 2), 'Lembar 1'. $I. $I,0)),"")
Saya perlu menambahkan kecocokan juga di CELL A1 di lembar 2
Lembar 1 memiliki daftar perusahaan yang besar dan data yang sesuai untuk setiap perusahaan, pada lembar 2, saya hanya ingin memilih perusahaan tertentu dari lembar 1 dan menunjukkan bidang dari baris yang saya pilih
Bagaimana Anda menarik data dari lembar lain berdasarkan kriteria di lembar?
Begini caranya. .
Di lembar asli tempat Anda ingin menarik data, tempatkan kursor di sel tempat Anda ingin memasukkan data
Ketik = (tanda sama dengan) ke dalam sel. Pilih lembar kedua dan kemudian sel yang berisi data yang ingin Anda masukkan ke lembar asli
Tekan Enter selesai
Bagaimana Anda membuat Excel menarik data dari banyak lembar?
Pada tab Data, di bawah Alat, klik Konsolidasi. Dalam kotak Fungsi, klik fungsi yang ingin digunakan Excel untuk menggabungkan data. Di setiap lembar sumber, pilih data Anda, lalu klik Tambahkan. Jalur file dimasukkan di Semua referensi
Bisakah Anda secara otomatis menarik data dari satu lembar Excel ke yang lain?
Otomatis data di lembar lain di Excel. Kita dapat menautkan lembar kerja dan memperbarui data secara otomatis . Tautan adalah rumus dinamis yang menarik data dari sel satu lembar kerja dan secara otomatis memperbarui data tersebut ke lembar kerja lain. Lembar kerja penghubung ini bisa berada di buku kerja yang sama atau di buku kerja lain.
Bagaimana cara menarik data dari lembar lain di Excel menggunakan Vlookup?
Pintasan vLookup .
Ketik awal rumus. =VLOOKUP( Panduan rumus akan muncul di bawah. .
Ikuti panduan dan masukkan setiap nilai. Ingatlah untuk menyisipkan koma di antara setiap nilai
Sisipkan tanda kurung tutup ) dan tekan Enter. .
Terakhir, salin dan tempel rumus untuk menarik email ke kolom lainnya
