XLOOKUP

Excel XLOOKUP 함수는 이전의 VLOOKUP, HLOOKUP 및 LOOKUP 등과 같은 함수를 대체하는 유연한 최신 함수입니다. XLOOKUP은 유사 일치 및 정확히 일치, 부분 일치에 대한 와일드카드(* ?) 사용을 지원하고, 세로 및 가로 범위에서의 조회를 지원합니다. (정확히 또는 유사) 일치하는 최적의 항목을 반환합니다.

구문 인수 설명 lookup_value (필수) 조회 값 lookup_array (필수) 검색할 배열 또는 범위 return_array (필수) 반환할 배열 또는 범위 [if_not_found] (선택 사항) 유효한 일치 항목이 없는 경우 제공한 [if_not_found] 텍스트를 반환합니다.유효한 일치 항목이 없고 [if_not_found]가 누락된 경우 #N/A가 반환됩니다. [match_mode] (선택 사항) 다음 일치 유형을 지정합니다.0 - 정확히 일치. 정확히 일치하는 항목이 없으면 #N/A를 반환합니다. 이 유형이 기본값입니다.-1 - 정확히 일치. 일치 항목이 없는 경우 다음으로 작은 항목을 반환합니다.1 - 정확히 일치. 일치 항목이 없는 경우 다음으로 큰 항목을 반환합니다.2 - *, ? 및 ~ 기호에 특별한 의미가 있는 와일드카드 일치 [search_mode] (선택 사항) 사용할 검색 모드를 지정합니다.1 - 첫 번째 항목에서 시작하는 검색을 수행합니다. 이 유형이 기본값입니다.1 - 마지막 항목부터 시작하는 역방향 검색을 수행합니다.2 - 오름차순으로 정렬된 lookup_array를 사용하는 이진 검색을 수행합니다. 정렬되지 않은 경우 잘못된 결과가 반환됩니다.2 - 내림차순으로 정렬된 lookup_array를 사용하는 이진 검색을 수행합니다. 정렬되지 않은 경우 잘못된 결과가 반환됩니다. 찾을 수 없음 메시지 XLOOKUP이 일치 항목을 찾을 수 없는 경우 Excel의 다른 일치 함수처럼 #N/A 오류를 반환합니다. 다른 일치 함수와 달리 XLOOKUP은 not_found라는 선택적 인수를 지원하며, #N/A 오류가 표시되어야 할 상황에서 이 인수를 사용하여 #N/A 오류를 재정의할 수 있습니다. not_found에 대한 일반적인 값은 "찾을 수 없음", "일치 항목 없음", "결과 없음" 등입니다. not_found에 대한 값을 제공할 때에는 텍스트를 큰따옴표("")로 묶어야 합니다. 참고: not_found에 대해 빈 문자열("")을 제공하는 경우 유의해야 합니다. 이때, 일치 항목이 없으면 XLOOKUP이 #N/A 대신 아무것도 표시하지 않게 됩니다. 일치 항목이 없는 경우 #N/A 오류를 표시하려면 이 인수를 완전히 생략해야 합니다. XLOOKUP의 이점 XLOOKUP은 특히, VLOOKUP에 비해 다음과 같은 여러 가지 중요한 이점을 제공합니다. XLOOKUP은 조회 값의 오른쪽 또는 왼쪽까지 데이터를 조회할 수 있습니다. XLOOKUP은 결과를 여러 개 반환할 수 있습니다(위의 예 #3). XLOOKUP의 기본값은 정확히 일치입니다(VLOOKUP의 기본값은 유사 일치임). XLOOKUP에는 세로 및 가로 데이터를 사용할 수 있습니다. XLOOKUP은 (마지막 항목에서 첫 번째 항목으로) 역방향 검색을 수행할 수 있습니다. XLOOKUP은 값 하나가 아니라 전체 행 또는 열을 반환할 수 있습니다.
window.onload = function() { var spread = new GC.Spread.Sheets.Workbook(_getElementById("ss")); spread.options.allowDynamicArray = true; initStyles(spread); initSpread(spread); }; function initSpread(spread) { spread.setSheetCount(9); spread.suspendPaint(); spread.suspendCalcService(); initSheet1(spread.getSheet(0)); initSheet2(spread.getSheet(1)); initSheet3(spread.getSheet(2)); initSheet4(spread.getSheet(3)); initSheet5(spread.getSheet(4)); initSheet6(spread.getSheet(5)); initSheet7(spread.getSheet(6)); initSheet8(spread.getSheet(7)); initSheet9(spread.getSheet(8)); spread.resumeCalcService(); spread.resumePaint(); } function initStyles(spread) { var introStyle = new GC.Spread.Sheets.Style(); introStyle.name = 'intro'; introStyle.font = 'normal bold 16px Segoe UI'; introStyle.foreColor = "#172b4d"; spread.addNamedStyle(introStyle); var introStyle1 = new GC.Spread.Sheets.Style(); introStyle1.name = 'intro1'; introStyle1.font = 'normal bold 14px Calibri'; introStyle1.hAlign = 0; introStyle1.vAlign = 1; introStyle1.foreColor = "#172b4d"; spread.addNamedStyle(introStyle1); var formulaStyle = new GC.Spread.Sheets.Style(); formulaStyle.name = 'formula'; formulaStyle.font = 'normal bold 12px Consolas'; formulaStyle.foreColor = "#c00000"; introStyle1.vAlign = 1; spread.addNamedStyle(formulaStyle); var tableHeaderStyle = new GC.Spread.Sheets.Style(); tableHeaderStyle.name = 'tableHeader'; tableHeaderStyle.font = "normal bold 14.7px Calibri"; tableHeaderStyle.hAlign = 1; tableHeaderStyle.backColor = "#d9e1f2"; spread.addNamedStyle(tableHeaderStyle); var tableContentStyle = new GC.Spread.Sheets.Style(); tableContentStyle.name = 'tableContent'; tableContentStyle.font = "normal normal 14.7px Calibri"; tableContentStyle.hAlign = 1; spread.addNamedStyle(tableContentStyle); var sourceStyle = new GC.Spread.Sheets.Style(); sourceStyle.name = 'source'; sourceStyle.hAlign = 0; sourceStyle.backColor = "#fce8ce"; spread.addNamedStyle(sourceStyle); var resultStyle = new GC.Spread.Sheets.Style(); resultStyle.name = 'result'; resultStyle.hAlign = 0; resultStyle.backColor = "#e2efda"; spread.addNamedStyle(resultStyle); } function initSheet1(sheet) { sheet.name('Use Case'); var table1Source = { name: 'Quarterly Employee Commissions', data: [ { salesRap: 'Jim', quarter: 'Q1', revenue: 351 }, { salesRap: 'Jim', quarter: 'Q2', revenue: 210 }, { salesRap: 'Kevin', quarter: 'Q1', revenue: 687 }, { salesRap: 'Sarah', quarter: 'Q1', revenue: 300 }, { salesRap: 'Sarah', quarter: 'Q2', revenue: 809 }, { salesRap: 'Kevin', quarter: 'Q2', revenue: 285 }, { salesRap: 'Bob', quarter: 'Q1', revenue: 110 } ] }; sheet.addSpan(1, 1, 1, 6); sheet.setValue(1, 1, table1Source.name); sheet.getCell(1, 1).hAlign(1).font("normal bold 15px Calibri"); sheet.setColumnWidth(1, 83); sheet.setColumnWidth(2, 73); sheet.setColumnWidth(3, 77); sheet.setColumnWidth(4, 122); sheet.setColumnWidth(5, 134); sheet.setColumnWidth(6, 98); var table1 = sheet.tables.add('Table1', 2, 1, 7, 6); table1.style(GC.Spread.Sheets.Tables.TableThemes.medium2); var table1Column1 = new GC.Spread.Sheets.Tables.TableColumn(1, "salesRap", "Sales Rap"); var table1Column2 = new GC.Spread.Sheets.Tables.TableColumn(2, "quarter", "Quarter"); var table1Column3 = new GC.Spread.Sheets.Tables.TableColumn(3, "revenue", "Revenue"); var table1Column4 = new GC.Spread.Sheets.Tables.TableColumn(4, null, "Comm Category"); var table1Column5 = new GC.Spread.Sheets.Tables.TableColumn(5, null, "Comm Percentage", "0%"); var table1Column6 = new GC.Spread.Sheets.Tables.TableColumn(6, null, "Commission"); table1.autoGenerateColumns(false); table1.bind([table1Column1, table1Column2, table1Column3, table1Column4, table1Column5, table1Column6], 'data', table1Source); var table2Source = { name: "Commissions Table", data: [ { category: 1, sales: 100, percentage: 0.05 }, { category: 2, sales: 200, percentage: 0.1 }, { category: 3, sales: 400, percentage: 0.15 }, { category: 4, sales: 800, percentage: 0.20 } ] }; sheet.addSpan(1, 8, 1, 3); sheet.setValue(1, 8, table2Source.name); sheet.getCell(1, 8).hAlign(1).font("normal bold 15px Calibri"); sheet.setColumnWidth(8, 88); sheet.setColumnWidth(9, 57); sheet.setColumnWidth(10, 91); var table2 = sheet.tables.add('Table2', 2, 8, 4, 3); table2.style(GC.Spread.Sheets.Tables.TableThemes.medium2); var table2Column1 = new GC.Spread.Sheets.Tables.TableColumn(1, "category", "Category"); var table2Column2 = new GC.Spread.Sheets.Tables.TableColumn(2, "sales", "Sales"); var table2Column3 = new GC.Spread.Sheets.Tables.TableColumn(3, "percentage", "Percentage", "0%"); table2.autoGenerateColumns(false); table2.bind([table2Column1, table2Column2, table2Column3 ], 'data', table2Source); table1.setColumnDataFormula(3, '=XMATCH([@Revenue],Table2[Sales],-1,1)'); table1.setColumnDataFormula(4, '=XLOOKUP([@[Comm Category]],Table2[Category],Table2[Percentage],0,0,1)'); table1.setColumnDataFormula(5, '=[@Revenue]*[@[Comm Percentage]]'); } function initSheet2(sheet) { sheet.name('basic exact match'); var intro = '#1 - basic exact match'; var formula = '=XLOOKUP(H5,B6:B10,D6:D10)'; sheet.setValue(1, 1, intro); sheet.setStyle(1, 1, 'intro'); sheet.setValue(2, 1, formula); sheet.setStyle(2, 1, 'formula'); var data = [ ["Movie","Year","Rank","Sales"], ["Fargo",1996,5,61], ["L.A. Confidential",1997,4,126], ["The Sixth Sense",1999,1,673], ["Toy Story",1995,2,362], ["Unforgiven",1992,3,159] ]; sheet.setArray(4, 1, data); for (var i = 0; i < data.length; i++) { for (var j = 0; j < data[i].length; j ++) { var styleName; if (i === 0) { styleName = 'tableHeader'; } else { styleName = 'tableContent'; } sheet.setStyle(4 + i, 1 + j, styleName); } } sheet.setColumnWidth(1, 126); sheet.setValue(4, 6, 'Movie'); sheet.setStyle(4, 6, 'source'); sheet.setValue(5, 6, 'Rank'); sheet.setStyle(5, 6, 'result'); sheet.setValue(4, 7, 'Toy Story'); sheet.setFormula(5, 7, formula); } function initSheet3(sheet) { sheet.name('basic approximate match'); var intro = '#2 - basic approximate match'; var formula = '=XLOOKUP(E6,B6:B10,C6:C10,,-1)'; sheet.setValue(1, 1, intro); sheet.setStyle(1, 1, 'intro'); sheet.setValue(2, 1, formula); sheet.setStyle(2, 1, 'formula'); var data = [ ["Quantily","Discount"], [0,0], [10,0.05], [25,0.1], [50,0.2], [100,0.25] ]; sheet.setArray(4, 1, data); for (var i = 0; i < data.length; i++) { for (var j = 0; j < data[i].length; j ++) { var styleName; if (i === 0) { styleName = 'tableHeader'; } else { styleName = 'tableContent'; } sheet.setStyle(4 + i, 1 + j, styleName); } } sheet.setValue(4, 4, 'Quantily'); sheet.setStyle(4, 4, 'source'); sheet.setValue(4, 5, 'Result'); sheet.setStyle(4, 5, 'result'); sheet.setValue(5, 4, 28); sheet.setFormula(5, 5, formula); } function initSheet4(sheet) { sheet.name('multiple value'); var intro = '#3 - multiple values'; var formula = '=XLOOKUP(B6,B9:B16,C9:E16)'; sheet.setValue(1, 1, intro); sheet.setStyle(1, 1, 'intro'); sheet.setValue(2, 1, formula); sheet.setStyle(2, 1, 'formula'); var data = [ ["ID","First","Last","Department"], [610,"Janet","Farley","Fulfillment"], [798,"Steven","Batista","Sales"], [841,"Evelyn","Monet","Fulfillment"], [886,"Marilyn","Bradley","Fulfillment"], [622,"Jonathan","Adder","Marketing"], [601,"Adrian","Birt","Engineering"], [869,"Julie","Irons","Sales"], [867,"Erica","Tan","Fulfillment"] ]; sheet.setArray(7, 1, data); for (var i = 0; i < data.length; i++) { for (var j = 0; j < data[i].length; j ++) { var styleName; if (i === 0) { styleName = 'tableHeader'; } else { styleName = 'tableContent'; } sheet.setStyle(7 + i, 1 + j, styleName); } } sheet.setColumnWidth(4, 100); sheet.setValue(4, 1, 'ID'); sheet.setStyle(4, 1, 'source'); sheet.setValue(4, 2, 'First'); sheet.setStyle(4, 2, 'result'); sheet.setValue(4, 3, 'Last'); sheet.setStyle(4, 3, 'result'); sheet.setValue(4, 4, 'Department'); sheet.setStyle(4, 4, 'result'); sheet.setValue(5, 1, 841); sheet.setFormula(5, 2, formula); } function initSheet5(sheet) { sheet.name('two-way lookup'); var intro = '#4 - two-way lookup'; var formula = '=XLOOKUP(I6,C5:F5,XLOOKUP(I5,B6:B10,C6:F10))'; sheet.setValue(1, 1, intro); sheet.setStyle(1, 1, 'intro'); sheet.setValue(2, 1, formula); sheet.setStyle(2, 1, 'formula'); var data = [ [null,"A","B","C","D"], ["Vinyl",10,11.5,13.23,15.21], ["Wood",12,13.8,15.87,18.25], ["Glass",15,17.25,19.84,22.81], ["Steel",18,20.7,23.81,27.38], ["Titanium",23,26.45,30.42,34.98] ]; sheet.setArray(4, 1, data); for (var i = 0; i < data.length; i++) { for (var j = 0; j < data[i].length; j ++) { var styleName; if (i === 0) { styleName = 'tableHeader'; } else { styleName = 'tableContent'; } sheet.setStyle(4 + i, 1 + j, styleName); } } sheet.setValue(4, 7, 'Material'); sheet.setStyle(4, 7, 'source'); sheet.setValue(4, 8, 'Glass'); sheet.setValue(5, 7, 'Group'); sheet.setStyle(5, 7, 'source'); sheet.setValue(5, 8, 'B'); sheet.setValue(6, 7, 'Result'); sheet.setStyle(6, 7, 'result'); sheet.setFormula(6, 8, formula); } function initSheet6(sheet) { sheet.name('not found message'); var intro = '#5 - not found message'; var formula = '=XLOOKUP(H4,B5:B9,E5:E9,"Not found")'; sheet.setValue(1, 1, intro); sheet.setStyle(1, 1, 'intro'); sheet.setValue(2, 1, formula); sheet.setStyle(2, 1, 'formula'); var data = [ ["Movie","Year","Rank","Sales"], ["Fargo",1996,5,61], ["L.A. Confidential",1997,4,126], ["The Sixth Sense",1999,1,673], ["Toy Story",1995,2,362], ["Unforgiven",1992,3,159] ]; sheet.setArray(4, 1, data); for (var i = 0; i < data.length; i++) { for (var j = 0; j < data[i].length; j ++) { var styleName; if (i === 0) { styleName = 'tableHeader'; } else { styleName = 'tableContent'; } sheet.setStyle(4 + i, 1 + j, styleName); } } sheet.setColumnWidth(1, 126); sheet.setValue(4, 6, 'Movie'); sheet.setStyle(4, 6, 'source'); sheet.setValue(5, 6, 'Rank'); sheet.setStyle(5, 6, 'result'); sheet.setValue(4, 7, 'Godzilla'); sheet.setFormula(5, 7, formula); } function initSheet7(sheet) { sheet.name('array arguments'); var intro = '#6 - array arguments'; sheet.setValue(1, 1, intro); sheet.setStyle(1, 1, 'intro'); sheet.setValue(4, 1, 'Years'); sheet.setStyle(4, 1, 'tableHeader'); sheet.setValue(5, 1, 'Sales'); sheet.setStyle(5, 1, 'tableHeader'); sheet.setValue(6, 1, 'Year'); sheet.setStyle(6, 1, 'source'); sheet.setValue(7, 1, 'Result'); sheet.setStyle(7, 1, 'result'); sheet.setValue(4, 2, '{1996,1997,1999,1995,1992}'); sheet.setValue(5, 2, '{61,126,673,362,159}'); sheet.setValue(6, 2, 1997); sheet.setColumnWidth(1, 126); var formula1 = '=XLOOKUP(C7,{1996,1997,1999,1995,1992},{61,126,673,362,159})'; sheet.setValue(2, 1, formula1); sheet.setStyle(2, 1, 'formula'); sheet.setFormula(7, 2, formula1); } function initSheet8(sheet) { sheet.name('return reference'); var intro = '#7 - return reference'; var formula = '=SUM(XLOOKUP(B6,B9:B13,E9:E13):XLOOKUP(C6,B9:B13,E9:E13))'; sheet.setValue(1, 1, intro); sheet.setStyle(1, 1, 'intro'); sheet.setValue(2, 1, formula); sheet.setStyle(2, 1, 'formula'); var data = [ ["Product","Qty","Price","Total"], ["Apple",23,0.52,11.9], ["Grape",98,0.77,75.28], ["Pear",75,0.24,18.16], ["Banana",95,0.18,17.25], ["Cherry",42,0.16,6.8] ]; sheet.setArray(7, 1, data); for (var i = 0; i < data.length; i++) { for (var j = 0; j < data[i].length; j ++) { var styleName; if (i === 0) { styleName = 'tableHeader'; } else { styleName = 'tableContent'; } sheet.setStyle(7 + i, 1 + j, styleName); } } // sheet.setColumnWidth(4, 100); sheet.setValue(4, 1, 'Start'); sheet.setStyle(4, 1, 'source'); sheet.setValue(4, 2, 'End'); sheet.setStyle(4, 2, 'source'); sheet.setValue(4, 3, 'Total'); sheet.setStyle(4, 3, 'result'); sheet.setValue(5, 1, 'Grape'); sheet.setValue(5, 2, 'Banana'); sheet.setFormula(5, 3, formula); } function initSheet9(sheet) { sheet.name('special cases'); var intro = '#test - special cases'; sheet.setValue(1, 1, intro); sheet.setStyle(1, 1, 'intro'); var data = [ ["Movie","Year","Rank","Sales"], ["Fargo",1996,5,61], ["L.A. Confidential",1997,4,126], ["The Sixth Sense",1999,1,673], ["Toy Story",1995,2,362], ["Unforgiven",1992,3,159] ]; sheet.setArray(1, 1, data); for (var i = 0; i < data.length; i++) { for (var j = 0; j < data[i].length; j ++) { var styleName; if (i === 0) { styleName = 'tableHeader'; } else { styleName = 'tableContent'; } sheet.setStyle(1 + i, 1 + j, styleName); } } sheet.setColumnWidth(1, 126); var formula1 = '=XLOOKUP(C2,C2:E2,C3:E3)'; sheet.setValue(8, 1, 'Row search'); sheet.setStyle(8, 1, 'intro1'); sheet.setValue(8, 2, formula1); sheet.setStyle(8, 2, 'formula'); sheet.setFormula(8, 5, formula1); var formula2 = '=XLOOKUP(C4,C2:C7,D2:D7)'; sheet.setValue(9, 1, 'Column Search'); sheet.setStyle(9, 1, 'intro1'); sheet.setValue(9, 2, formula2); sheet.setStyle(9, 2, 'formula'); sheet.setFormula(9, 5, formula2); var formula3 = '=XLOOKUP(B5,B2:B7,C2:E2)'; sheet.setValue(10, 1, 'Not same direction'); sheet.setStyle(10, 1, 'intro1'); sheet.setValue(10, 2, formula3); sheet.setStyle(10, 2, 'formula'); sheet.setFormula(10, 5, formula3); var formula4 = '=XLOOKUP(B3,B3,E5)'; sheet.setValue(11, 1, 'Single Search'); sheet.setStyle(11, 1, 'intro1'); sheet.setValue(11, 2, formula4); sheet.setStyle(11, 2, 'formula'); sheet.setFormula(11, 5, formula4); var formula5 = '=XLOOKUP(B5,C4,D6)'; sheet.setValue(12, 1, 'Single Search'); sheet.setStyle(12, 1, 'intro1'); sheet.setValue(12, 2, formula5); sheet.setStyle(12, 2, 'formula'); sheet.setFormula(12, 5, formula5); var formula6 = '=XLOOKUP(D2,B2:E2,B3:E7)'; sheet.setValue(13, 1, 'Spill Column'); sheet.setStyle(13, 1, 'intro1'); sheet.setValue(13, 2, formula6); sheet.setStyle(13, 2, 'formula'); sheet.setFormula(13, 5, formula6); } function _getElementById(id) { return document.getElementById(id); }
<!doctype html> <html style="height:100%;font-size:14px;"> <head> <meta charset="utf-8" /> <meta name="spreadjs culture" content="ko-kr" /> <meta name="viewport" content="width=device-width, initial-scale=1.0" /> <link rel="stylesheet" type="text/css" href="$DEMOROOT$/ko/purejs/node_modules/@mescius/spread-sheets/styles/gc.spread.sheets.excel2013white.css"> <script src="$DEMOROOT$/ko/purejs/node_modules/@mescius/spread-sheets/dist/gc.spread.sheets.all.min.js" type="text/javascript"></script> <script src="$DEMOROOT$/ko/purejs/node_modules/@mescius/spread-sheets-resources-ko/dist/gc.spread.sheets.resources.ko.min.js" type="text/javascript"></script> <script src="$DEMOROOT$/spread/source/js/license.js" type="text/javascript"></script> <script src="app.js" type="text/javascript"></script> <link rel="stylesheet" type="text/css" href="styles.css"> </head> <body> <div class="sample-tutorial"> <div id="ss" class="sample-spreadsheets"></div> </div> </body> </html>
input[type="text"] { width: 200px; margin-right: 20px; } label { display: inline-block; width: 110px; } .sample-tutorial { position: relative; height: 100%; overflow: hidden; } .sample-spreadsheets { width: 100%; height: 100%; overflow: hidden; float: left; } label { display: block; margin-bottom: 6px; } input { padding: 4px 6px; } input[type=button] { margin-top: 6px; display: block; width:216px; } body { position: absolute; top: 0; bottom: 0; left: 0; right: 0; } code { border: 1px solid #000; }