Index Function - Use One Formula to Generate the Result List with Two Variables
I will use index to locate specific value in the range.
The Range
| Col1 | Col2 | Col3 | Col4 |
|---|---|---|---|
| Tom | Apple | Orange | Banana |
| John | Earl | Camomile | Sri Lanka |
| Peter | Van | Bus | Tram |
| Patrick | A4 | A3 | A5 |
The row number and column number is kept in this table. These numbers are subject to changes.
Index Function Parameter
| Row | Column |
|---|---|
| 2 | 3 |
| 3 | 2 |
| 4 | 4 |
| 2 | 2 |
| 4 | 3 |
| 3 | 1 |
Anticipated Result
| Anticipated Result |
|---|
| Orange |
| Earl |
| Tram |
| Apple |
| Bus |
| John |
Please see my shared spreadsheet: https://docs.google.com/spreadsheets/d/12JDr0J3Sbu-ZUpWveMswnPXOtP4EcA-eZzxFsV2ALlE/edit?usp=sharing
It is straightforward to use formula by inputting formulas alongside each pair of parameter. However, I want to make it simple. Can I input one formula in cell D11 to achieve the same result?
Solution
Using BYROW:
You can also use BYROW as an alternative solution.
=BYROW(A11:B16,LAMBDA(x,INDEX(A2:D5, INDEX(x,,1),INDEX(x,,2))))
Sample Output:
Reference/s:
- Check if Cell Contains Month and Compare to Date
- Arrayformula with Query in Google Sheets
- Conditional borders troubleshooting
- Troubleshooting Spreadsheet Form Submit Events Google
- Macro for replacing data and then collating it?
- QUERY Formula referring to the value of a cell rather than a constant piece of text
- Index Function - Use One Formula to Generate the Result List with Two Variables
- google sheets IMPORTXML for currency prices
- How to add plus 5 hour to the copy Cell and past in other with plus time
- How to connect Google Sheets to Database
- Merging Google Sheets formulas into1 to get the same Result
- TypeError: UrlFetchApp.fetch(...).then is not a function
- Using Column Headers with COUNTIF To Add those that Fall Between Dates in Cells
- Using SUMIFS with MAP/LAMBDA
- Deal with line break while using IMPORTXML in Google Sheet
- What is an equivalent for INDEX in ARRAYFORMULA?
- Google Sheet Script - Removing a gap of blank cells within a range to keep a list compact and tidy?
- For loop running only for first element
- How can I use a Custom Function in Formulas across Multiple Spreadsheets?
- How to use a filter inside SortN
- Problems with ShinyApp and Google authentification, when making Public
- Newbie Help: Syntax error: SyntaxError: Unexpected identifier (JavaScript)
- Error in Google App Script when submitting form data to Google Sheets
- Failed to submit form responses from a spreadsheet
- Maps.newDirectionFinder().SetArrive & SetDepart (Google Maps API) in GSheets App Script aren't impacting returned driving times w/ tested scripts
- Check if range is empty and copy data from Range
- How to convert duration of MM:SS to seconds in Google Sheets?
- Conditional formatting comparing cell to cell values in a range
- showModalDialog in SpreadsheetApp Won't Close
- Google Sheets Create Template