vlookup กับข้อมูลเยอะ ๆ

[wthn]

คือมีปัญหากับการใช้ vlookup กับข้อมูลเยอะ ๆ ไม่รู้ว่าจะสามารถทำได้หรือไม่ แล้วอยากให้ช่วยหาค่าในคอลัมน์ CA ใน sheet 2 ที่มีค่าในคอลัมน์ Batch number และ Doc num ที่ตรงกันกับ Batch และ Doc ใน sheet 1
ไม่รู้จะรบกวนไปมั้ย

[snasui]

งดใช้ภาษาแชทในฟอรัมครับ

ผมทำตัวอย่างการ Match ข้อมูลให้แล้วตามไฟล์แนบ โดยในชีท 2 ให้เพิ่มคอลัมน์เพื่อสำหรับ Match ข้อมูลจากนั้นใช้สูตร Match มาช่วย และกรองมาใช้เฉพาะค่าที่ได้ True (แสดงว่าเข้าเงื่อนไขตามต้องการ)

[wthn]

หากเราต้องการจะให้ในชีทที่ 1 คอลัมน์ CA แสดงค่าเหมือนในคอลัมน์ CA ในชีทที่ 2 ที่มีค่าในคอลัมน์ Batch number และ Doc number ที่ตรงกันทั้งสองชีท ควรจะต้องใช้สูตรอะไรครับ ช่วยแนะนำด้วย ขอบคุณครับ

[snasui]

ที่ชีท 1 เซลล์ G2 คีย์สูตร

=SUMPRODUCT(--('2'!$C$2:$C$1019=B2),--('2'!$D$2:$D$1019=C2),'2'!$F$2:$F$1019)

Enter > Copy ลงด้านล่าง

[wthn]

ไม่ต้องการให้ค่าที่ได้เป็นผลรวมครับ อยากให้เป็น line by line มากกว่าครับ ต้องแก้สูตรอย่างไรครับ

[snasui]

ลองตามนี้ครับ

ที่เซลล์ G2 คีย์สูตร

=Index('2'!$F$2:$F$1019,Match(1,If('2'!$C$2:$C$1019=B2,If('2'!$D$2:$D$1019=C2,1)),0))

Ctrl+Shift+Enter > Copy ลงด้านล่าง

[wthn]

error ครับ ตามไฟล์ที่แนบมา

[snasui]

กดแป้นไม่ถูกต้องครับ

ลองตามนี้ครับ

ที่เซลล์ G2 คีย์สูตร

=Index('2'!$F$2:$F$1019,Match(1,If('2'!$C$2:$C$1019=B2,If('2'!$D$2:$D$1019=C2,1)),0))

Ctrl+Shift+Enter > Copy ลงด้านล่าง

[wthn]

ถ้าหากอยากได้เป็นค่าแบบบรรทัดต่อบรรทัดเลยล่ะครับ ไม่เอาค่าเฉพาะของบรรทัดแรก เช่นในชีท 2 มีข้อมูลในคอลัมน์ CA อยู่ 9 บรรทัด ไปใส่ในชีท 1 ตามบรรทัดของแต่ละตัวเลยครับ ไม่รู้ว่าแบบนี้จะใช้ vlookup ได้มั้ยครับ

[snasui]

ลองดูสูตรตามด้านล่างครับ ซึ่งค่าที่ไม่ตรงกันจะแสดงค่า 0

ที่ G2 คีย์

=LOOKUP(9.99999999999999E+307,CHOOSE({1,2},0,INDEX('2'!$F$2:$F$1019,SMALL(IF('2'!$C$2:$C$1019=B2,IF('2'!$D$2:$D$1019=C2,ROW('2'!$A$2:$A$1019)-ROW('2'!$A$2)+1)),SUMPRODUCT(--('2'!C$2:C2='2'!C2),--('2'!D$2:D2='2'!D2))))))

Ctrl+Shift+Enter > Copy ลงด้านล่าง

Vlookup เป็นการ Lookup แบบตัวต่อตัว ไม่สามารถนำมาใช้กับงานแบบนี้ได้ครับ

[wthn]

ยังไม่ได้ค่าตามที่ต้องการเลยครับ ยังคงแสดงค่าทั้งหมดด้วยค่าบรรทัดแรก ยังไงช่วยหาคำตอบให้ด้วยนะครับ ต้องการใช้มากเพราะข้อมูลจริง ๆ เยอะมาก คงจะต้องพึ่งสูตรจากพี่แล้วล่ะครับ (ดูผลได้จากไฟล์ที่แนบไฮไลท์สีเหลือง)

[snasui]

อ้างตำแหน่งเซลล์ผิดไปครับ ได้ปรับตำแหน่งเซลล์ใหม่และปรับสูตรให้กระชับลง

ที่ G2 คีย์

=LOOKUP(9.99999999999999E+307,CHOOSE({1,2},0,INDEX('2'!$F$2:$F$1019,SMALL(IF('2'!$C$2:$C$1019=B2,IF('2'!$D$2:$D$1019=C2,ROW('2'!$A$2:$A$1019)-ROW('2'!$A$2)+1)),SUM(IF(B$2:B2=B2,IF(C$2:C2=C2,1)))))))

Ctrl+Shift+Enter > Copy ลงด้านล่าง

[wthn]

สำเร็จแล้ว ขอบคุณมากครับ แต่ขอถามเป็นความรู้นะครับ ว่าทำไมเราต้องใช้ Ctrl+Shift+Enter แล้วค่อยก๊อปปี้ ทำไมก๊อปปี้ลงมาเลยไม่ได้ล่ะครับ

[snasui]

ประเด็นของการ Ctrl+Shift+Enter เป็นการ Enter ให้รับสูตรแบบ Array ครับ ถ้าไม่กด 3 แป้นก็จะไม่ได้ผลลัพธ์ตามต้องการ

ไม่ใช่ว่าต้อง Ctrl+Shift+Enter แล้วค่อย Copy เสมอ ถ้าสูตรถูกเขียนไว้แล้วและต้องการนำไปใช้ที่อื่น ๆ ก็ Copy ไปได้เลย ที่ผมเขียนเป็น Ctrl+Shift+Enter > Copy ลงด้านล่าง เพราะเป็นขั้นตอนที่ต่อเนื่องกันเท่านั้นครับ

[wthn]

แบบว่าเกิดปัญหาแล้วครับผม พอลองไปใช้งานจริง ไฟล์ที่ใช้เทียบกันดันเป็นคนละไฟล์ ไม่ได้อยู่ชีทเดียวกันเหมือนตัวอย่างที่ให้ไป จะต้องปรับสูตรยังไงครับ เพราะลำพังจะลองแก้สูตรเองก็งงกับสูตรที่ยาวมากกก

[snasui]

ลองแบบนี้ครับจะได้ไม่ต้องเขียนหรือปรับสูตรแบบอ้างอิงข้ามไฟล์ใหม่

1. Copy ค่าจากไฟล์ที่ 2 มาวางในไฟล์ที่ 1 ทับค่าเดิมที่มีอยู่แล้ว
2. ปรับช่วงข้อมูลในสูตรให้ครอบคลุมช่วงข้อมูลจริงที่นำมาวาง
3. Cut ข้อมูลที่นำมาวางกลับไปยังไฟล์ที่ 2