PDF Mikata

Build an XLOOKUP Formula

When you want a more flexible lookup than VLOOKUP.

What you enter is never uploaded. Everything is processed on this device.

  1. 1Enter the key and the ranges
  2. 2Choose the kind
  3. 3Copy the formula

Done

🔒 Your files are never sent to an external server. Everything is processed safely inside this browser.

How to use

  1. Enter the value to find, the column to search and the column to return.
  2. Choose the kind.
  3. Copy the formula and paste it into a cell.

How it works

  • XLOOKUP gives the value in the return column on the row it finds in the search column.
  • XMATCH gives the position number it finds.

Notes

  • LAST searches from the bottom, APPROX looks for the nearest smaller value and REVERSE returns the left column from the right one.
  • The second formula works in older Excel that lacks XLOOKUP.

FAQ

Is what I enter sent anywhere?

No. Everything is processed inside the browser on your device.

Do I need to upload a file?

No. The formula is built only inside the browser.

If PDF Mikata was useful, a small tip would mean a lot.

Send a tip (¥300)Send a gift (Amazon wishlist)

Guides for this tool

How to Write XLOOKUP (Build It Automatically, Free)Looking Up Right to LeftSearching from the BottomFinding the Nearest ValueLooking Up in Older ExcelWhat to Do When It Does Not WorkXLOOKUP Builder Without Uploading

More tools

Photos to PDFCombine several photos into one PDF in the order you choose. You can rotate, reorder and set a size limit.Merge PDFsCombine several PDF files into one, in the order you choose.Extract or Delete PDF PagesKeep only the pages you need, or remove the pages you don't.Compress PDFShrink a PDF to fit under a size you choose, for upload limits.PDF to ImagesSave each page of a PDF as a JPEG or PNG image.Convert Image Format (JPG, PNG, WebP)Convert images to JPG, PNG or WebP, several at once.Rotate & Reorder PDF PagesFix sideways pages, change the page order, or remove pages.Add Page Numbers to PDFAdd page numbers to a PDF, choosing position and style.Compress & Resize ImagesMake image files smaller, and resize them if you like.Convert HEIC to JPGConvert HEIC photos from iPhone to JPG or PNG.Split PDFSplit a PDF into several files: one page each, every N pages, or by ranges.Crop & Rotate ImageCrop an image to any area or ratio, and rotate it.Extract Text from PDFPull the text out of a PDF to copy or save.Create a Receipt PDFFill in the payer, amount and description to make a receipt PDF. Tax breakdown and invoice registration number are optional.Create a Resume PDF (Japanese Rirekisho)Fill in your details, education, work history and motivation to make a Japanese-style resume (rirekisho) PDF, with an optional ID photo.Create a Timetable / Weekly ScheduleEnter classes, jobs and plans to make a timetable or weekly schedule as a PDF or image, with color coding.Create Business Cards / Name CardsEnter your name and details to make business or name cards laid out on A4 as a PDF. You can also make name tags in bulk from a list.Create a QR Code (URL, Wi-Fi)Make a QR code from a URL, text or Wi-Fi details and save it as PNG, SVG or PDF.Combine PDF Pages onto One Sheet (2-up, 4-up)Arrange several PDF pages on one A4 sheet (2, 4, 6, 8 or 9 up) to save paper when printing.Convert a PDF to Grayscale (Black & White)Turn a color PDF into grayscale or pure black and white, ready for monochrome printing, copying or fax. Choose the resolution.Add a Text Watermark to PDFAdd text such as "For submission", "Copy" or "Confidential" lightly across every PDF page. Choose position, color and opacity.Crop PDF MarginsTrim PDF margins automatically or by setting each edge. Easier to read on a phone, cleaner to print.Black Out (Redact) Parts of a PDFDrag over the parts of a PDF you want to hide and black them out. Redacted pages become images, so the text underneath is gone.View and Remove PDF Properties (Author, Title)See the author, title, software and dates stored in a PDF, then remove or edit them.Fill In a PDF: Add Text, Date, Signature and MarksAdd text, a date, check marks, circles, a handwritten signature or an image (such as a seal) anywhere on a PDF.Resize PDF Pages to A4 or Another Paper SizeMake PDF pages of mixed sizes all the same size, such as A4, A3, B5 or Letter. Text and graphics stay sharp.Delete Pages from a PDFChoose and delete unwanted pages from a PDF.Extract Pages from a PDFPick the pages you need and save them as a new PDF.Reverse the Page Order of a PDFReverse the page order of a PDF.Insert Blank Pages into a PDFInsert blank pages anywhere in a PDF.Split PDF Spreads into Single PagesSplit spreads in a PDF into single pages.Add a Header and Footer to a PDFAdd a title, date or page numbers to the top and bottom of a PDF.Arrange a PDF as a Booklet for PrintingReorder a PDF so it prints and folds into a booklet.Find and Remove Blank Pages in a PDFFind blank pages in a PDF automatically and delete them.Turn a PDF into One Long ImageJoin all pages of a PDF into one tall image.Merge Front and Back Scans into One PDFInterleave a fronts PDF and a backs PDF into one PDF.Resize Images by Pixel SizeResize images by width, height or percentage.Rotate and Flip ImagesRotate images and flip them horizontally or vertically.Add a Text Watermark to ImagesOverlay watermark text such as "Copy" on images.Mosaic or Blur Part of an ImageHide parts of an image with mosaic, blur or a black box.Remove Location and Shooting Data from PhotosRemove GPS, date and camera data from photos and save a clean copy.Apply Black-and-White, Sepia and Other LooksTurn images black and white or sepia, or change brightness.Make a Favicon (Site Icon)Make a complete favicon set from one image.Join Several Images into OneStack or join several images into one image.Crop to ID Photo Size and Tile for PrintingCrop a photo to ID photo size and tile it on an L-size sheet.Pick Color Codes from an ImageRead color codes from an image and find the main colors.Character Counter (for Applications and Reports)Count characters, lines and manuscript sheets of your text.Convert Between Western Years and Japanese ErasConvert between AD years and Japanese eras.Convert Full-Width, Half-Width, Hiragana and KatakanaConvert full-width/half-width and hiragana/katakana.Remove and Tidy Line Breaks in TextTidy line breaks in text copied from PDFs and elsewhere.Calculate Consumption Tax (Incl. / Excl. Tax)Calculate prices incl. and excl. tax.Calculate Age, Days Between Dates and Date OffsetsCalculate age and days between dates.Remove a Password from a PDFRemove the password and restrictions from a PDF whose password you know.Add a Password to a PDFAdd a password needed to open a PDF.Read Text from Images and PDFs (OCR)Turn text in images and scanned PDFs into text.Compare Two PDFs (Find Differences)Show the text differences between two PDFs, color-coded.Generate a Strong Random PasswordGenerate strong random passwords instantly.Calculate Percentages, Discounts and % ChangeCalculate percentages, discounts and % change instantly.Remove Duplicate Lines and Sort a ListRemove duplicate lines and sort them.Unit Converter (Length, Weight, Area, Temperature and More)Convert length, weight, area, temperature and more instantly.Make a ZIP FilePack several files into one ZIP.Open a ZIP File (Unzip)See what is inside a ZIP and extract the files you need.Check a File's Hash (SHA-256)Calculate a file's hash and compare it.Convert CSV and JSONConvert between CSV and JSON instantly.Convert SVG to PNGConvert an SVG file to a PNG image.Convert Files and Base64Convert between files and Base64 text.Format and Validate JSONFormat, minify and validate JSON.Compare Two Texts (Diff)Show the differences between two texts line by line.Convert Excel to CSV or JSONConvert an Excel sheet to CSV, TSV or JSON.Convert CSV to Excel (.xlsx)Convert a CSV to an Excel (.xlsx) file.Extract Text from a Word FileExtract the plain text from a Word file.Extract Text from PowerPointExtract the text of each PowerPoint slide.Put PDF Text into WordExtract PDF text into a Word file.Put Images into a Word FilePut images into a Word file, one per page.Convert Excel Formulas to ValuesReplace Excel formulas with their values.Remove Author Info from Office FilesClear the author and other info stored in Office files.Extract Images from Word, Excel and PowerPointExtract images from Office files as a ZIP.Convert Word to HTMLTurn Word text into HTML.Split an Excel File by SheetSplit each Excel sheet into its own CSV or TSV.Convert an Excel Table to HTML or MarkdownConvert an Excel table to an HTML or Markdown table.Convert Text Encoding (Fix Garbled Text)Convert text files between Shift_JIS, UTF-8, EUC-JP and more to fix garbled text.Split an Image into a Grid (for Social Posts)Split an image into equal pieces across and down and save them.Make an Animated GIF from ImagesJoin several images in order into an animated GIF.Rename Many Files at Once (Numbers, Dates)Rename many files at once with numbers or dates.Merge, Split and Pick Columns from CSVMerge CSV files, split by rows, or keep only chosen columns.Extract Images from a PDFExtract the images embedded in a PDF at their original quality.Find Duplicate FilesFind files with identical contents and save a ZIP without duplicates.Check a File's Real Type (Extension vs Contents)Judge a file's type from its contents and check that the extension matches.Encrypt and Decrypt a File with a PasswordEncrypt and decrypt any file with a password (AES-256).Merge and Split Text FilesMerge text files into one or split them by lines or size.Merge Several Excel Files into One CSVStack the data of several Excel files into one CSV.View and Add PDF BookmarksView and add bookmarks (table of contents) of a PDF.Trim an Audio FileCut out part of an audio file and save it as WAV.Save Video Frames as ImagesSave any moment of a video as an image.URL Encode and DecodeEncode and decode text in URLs.Convert Markdown to HTMLConvert Markdown to HTML.Random Picker, Order and GroupsDraw names, set a random order or split groups.Color Code Converter (HEX, RGB, HSL)Convert color codes between HEX, RGB and HSL.UNIX Timestamp ConverterConvert between UNIX time and dates.Regex TesterTest regular expressions with matches and replacements.Text Case Converter (camelCase, snake_case)Convert text case and naming styles.Base64 Text Encoder and DecoderConvert text to and from Base64.UUID Generator (v4, in bulk)Generate UUIDs (v4) in bulk.Dummy Text Generator (Japanese, Lorem ipsum)Generate placeholder text for design checks.HTML Escape and UnescapeEscape and unescape HTML special characters.BMI Calculator (with Reference Weight)Calculate BMI from height and weight.Age Calculator (years, months, days)Calculate age from a date of birth.Split the Bill Calculator (with rounding)Split a total amount among people.Work Hours Calculator (with break and pay)Calculate working time from start and end times.Google Drive Direct Link ConverterConvert a Drive share link into a direct link.Google Docs, Sheets and Slides Export Link MakerMake export links for Google files.Google Calendar Add-Event Link Maker (with ICS)Make an add-to-calendar link.Excel Column Number and Letter ConverterConvert Excel column numbers and letters.Excel Serial Number and Date ConverterConvert Excel serial numbers and dates.Excel Table to Markdown, HTML, CSV and JSONConvert a pasted table to Markdown, HTML, CSV or JSON.Swap Rows and Columns of a TableSwap the rows and columns of a pasted table.Remove Extra Spaces and Invisible Characters from Excel TextRemove extra spaces and invisible characters.Count Cells, Rows and Columns in an Excel RangeFind the cells, rows and columns of a range.Convert a Markdown Table for ExcelTurn a Markdown table into a table for Excel.Split Text into Columns by a DelimiterSplit text into columns by a delimiter.Build a VLOOKUP or XLOOKUP FormulaBuild VLOOKUP and XLOOKUP formulas.Sum, Average and Median of a Column of NumbersGet the sum and average of pasted numbers.Build an Excel TEXT FormulaBuild a TEXT formula.Build a TEXTJOIN FormulaBuild a TEXTJOIN formula.Build a SUMIF or COUNTIF FormulaBuild SUMIF and COUNTIF formulas.Check an Excel Sheet NameCheck whether a sheet name is allowed.Build an IF FormulaBuild an IF formula.Build a DATEDIF FormulaBuild a DATEDIF formula.Build an IMPORTRANGE FormulaBuild an IMPORTRANGE formula.Build a FILTER FormulaBuild a FILTER formula.Build a QUERY FormulaBuild a QUERY formula.Build a Rounding FormulaBuild ROUND-style formulas.Build a LEFT, MID or RIGHT FormulaBuild text extraction formulas.Build a Date Offset FormulaBuild date offset formulas.Build a Rank FormulaBuild rank formulas.Build an IMPORTHTML FormulaBuild IMPORTHTML-style formulas.Build an INDEX and MATCH FormulaBuild INDEX and MATCH formulas.Build a SUBSTITUTE FormulaBuild text replacement formulas.Build a Conditional Formatting FormulaBuild conditional formatting formulas.Build an ARRAYFORMULABuild ARRAYFORMULA formulas.Build a SPLIT FormulaBuild text splitting formulas.Build a UNIQUE FormulaBuild duplicate-handling formulas.Build a NETWORKDAYS FormulaBuild working-day formulas.Build a SUMPRODUCT FormulaBuild SUMPRODUCT formulas.Build a SPARKLINE FormulaBuild SPARKLINE formulas.Build a HYPERLINK FormulaBuild HYPERLINK formulas.Build a Regular Expression FormulaBuild regular expression formulas.Build a SORT FormulaBuild sorting formulas.Build a LARGE and SMALL FormulaBuild formulas for the Nth largest or smallest value.Build a SWITCH FormulaBuild formulas that return a result for each value.Build a SEQUENCE FormulaBuild formulas for number and date series.Build a PMT FormulaBuild loan payment formulas.Build a Text Case and Trim FormulaBuild formulas that fix letter case and spaces.Build a WEEKDAY FormulaBuild weekday and week number formulas.Build a FIND FormulaBuild formulas that find text and count characters.Build a TRANSPOSE FormulaBuild formulas that swap rows and columns.Build a COUNTIF FormulaBuild formulas that count cells meeting a condition.Build an AVERAGE and MEDIAN FormulaBuild average, median and other statistics formulas.Build a Text to Date FormulaBuild formulas that turn text into dates and numbers.Build a Percentage FormulaBuild percentage and rate of change formulas.Build a SUBTOTAL FormulaBuild formulas that total only the visible rows.Build an INDIRECT FormulaBuild formulas that call up a cell from a sheet name or a shift.Build an ISBLANK FormulaBuild formulas that check whether a cell is empty, a number or an error.