Build an INDEX and MATCH Formula

When VLOOKUP cannot return a column to the left.

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

1Enter the ranges and the value
2Choose the match type
3Copy the formula

Range to return from (such as C2:C100) Range to search in (such as B2:B100) Cell with the value to find (such as F2) Match type Header range (for a table lookup, may be empty) Cell with the header value (may be empty) INDEX and MATCH formula Formula that shows blank if not found

Done

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

Tools often used together

Build a VLOOKUP or XLOOKUP FormulaBuild VLOOKUP and XLOOKUP formulas.

Build a FILTER FormulaBuild a formula that returns the rows that match.

Build a SUMIF or COUNTIF FormulaBuild formulas for conditional totals and counts.

Merge PDFsCombine PDFs into one.

See all tools (back to the home page)

How to use

Enter the return range, the search range and the cell with the value.
Choose the match type. For a table lookup, also enter the header range.
Copy the formula and paste it into a cell.

How it works

MATCH finds the position and INDEX returns the value there.
A version that shows blank when nothing is found is built too.

Notes

EXACT is an exact match, LESS the nearest value at or below, and GREATER the nearest at or above.
Ranges are fixed with $ so they do not shift when copied down.

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 INDEX and MATCH (Build Them Automatically, Free)Returning a Column to the Left Two-Way Lookup Finding the Nearest Value When Nothing Is Found What to Do When It Does Not Work INDEX MATCH 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 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.