I am not getting correct results from my lookup formula. Say I wanted to choose the letter A, or D, or F, or M and its corresponding number i.e. 1, 4, 1, 3. The lookup formula only returns correct values for the first row of letters. Can anyone help. Should be simple enouhg, but it appears it only looks at one row and no further but makes up incorrect random numbers
| Header 1 | Header 2 | Header 3 | Header 4 | Header 5 |
|---|---|---|---|---|
| 1 | 2 | 3 | 4 | 5 |
| A | B | C | D | E |
| F | G | H | I | J |
| K | L | M | N | O |
Rearrange your data as shown below in the Key table. Enter your request and calculate the result in a separate table, using the formula(s) provided below.
Formula(s)
The formula used in Request is shown between the two Request tables.
LOOKUP(A1,Key::$A,Key::$B)
As can be seen, this formula returns an error message if the cell above it is empty.
Request 2 contains the same formula, enclosed in IFERROR, used as an error trap to catch the error messages above and replace them with a null string ( "" ), which gives the appearance of an empty cell.
IFERROR(LOOKUP(A1,Key::$A,Key::$B),"")
Each formula ins entered in cell A2 of its table, then filled right to the end of row 2.
All values shown in Key are entered from the keyboard.
Regarding LOOKUP:
The function browser entry for LOOKUP does mention the possibility of using a two-dimensional array for the search-where and/or the result-values, and gives illustrative examples of each. Take a close read of the description and notes if you want to use the original layout of your table.
To open the Function Browser, click on any cell in a table and type =.
Regards,
Barry
What is the formula you are using?
What specifically, do you expect the formula to do?
Can you provide three different examples of an input value and the expected output value?
All responses should be based on the table in your initial post.
Regards,
Barry
| 1 | 2 | 3 | 4 | 5 | 6 | 7 | 8 | 9 |
|---|---|---|---|---|---|---|---|---|
| A | B | C | D | E | F | G | H | I |
| J | K | L | M | N | O | P | Q | R |
| S | T | U | V | X | Y | Z | ||
| B | A | L | D | Y | L | |||
| 2 | 1 | 3 | D | 6 | 3 | |||
RESULT wanted to find Corresponding numbers for the Letters spelling BALDYL . The number below the name in the Grid above is what I would expect to see. I use the following formular:
LOOKUP(A5,A$2$:I$4$,A$1$:I$1$)
THIS FORMULAR ONLY FINDS THE CORRESPONDING NUMBERS TO THE LETTERS IF THEY APPEAR IN ROW2 IN THIS EXAMPLE.
CAN YOU HELP PROVIDE THE CORRECT FORMULAR
Horizontal to Vertical isn't the significant change. What's significant is the change from a two dimensional search field and single dimension return field to a matched pair of single dimensional fields.
Here's the matching solution, done horizontally:
Regards,
Barry
You need to explain what it is you are doing exactly. We don't know if you don't tell us.
To get an absolute reference from a specific cell prefix the location with "$" for example $C$4.
You misread my example above. See my response to Barry
Fantastic. Many thanks. I never thought of changing the table from horizontal to verical.
LOOKUP MULTIPLE COLUMNS AND ROWS
