INDEX MATCH Two-Way Lookup Builder
Find a value at the intersection of a row and column.
The Open buttons send the prompt text as currently shown below to that site only when you click. Fill in the placeholders first.
Build a two-way lookup. App: [SPREADSHEET_APP]. Table layout: row labels in [ROW_LABEL_RANGE], column headers in [COLUMN_HEADER_RANGE], values in [VALUE_RANGE]. Cells holding the row and column choices: [ROW_INPUT_CELL] and [COLUMN_INPUT_CELL]. Sample table (fake data): [SAMPLE_TABLE]. Provide: 1. The formula with INDEX and two MATCH functions: =INDEX(values, MATCH(row_choice, row_labels, 0), MATCH(column_choice, column_headers, 0)). 2. A step-by-step explanation showing what each MATCH returns for a sample choice from my table, so I can verify the position numbers by hand. 3. A version with XLOOKUP nested for modern versions. 4. Data validation lists for the two input cells so users cannot mistype. 5. Error handling: what to show when a label is missing, and a check cell that flags duplicates in labels. 6. Behavior with merged cells, trailing spaces, and numbers stored as text, and how to fix each. 7. A rate-card example (shipping cost by weight band and region) with the expected result for three inputs. 8. Approximate-match version for bands (MATCH with 1 for sorted ascending data) and its risks if the data is unsorted. Include a test table of four cases with expected answers.
Fill in before use
Optional: type your details and the prompt above updates. Nothing is saved or sent.
Replace each bracketed placeholder with your own details, then paste into Any AI or another assistant.