Numbers arrays or ifstatement

For this table, Try below for G1 cell,

1. Copy (command-C) either one of the next lines of formula.

IFERROR(VLOOKUP(F;$A:$B;2;0);IFERROR(VLOOKUP(F;$C:$D;2,0);"-"))

IFERROR(INDEX($B;MATCH(F;$A;0));IFERROR(INDEX($D;MATCH(F;$C;0));"-"))

2. Select the cell and type "=" to bring up the input field for formula.

3. Paste (Type command-V) and type [return] key.

Note:

• The formulas above use semicolons for list delimiter.
• "-" will be placed if no matching data is found.
• Absolute reference is used for column A to D, but relative one for column F.

Hi Mark,

Rows run left to right, and are labeled with numbers.

Columns run top to bottom, and are labeled with letters.

Your reference columns include single letters in column A, with the associated number in the same row of column B,

and letter pairs in some rows of column C, with the associated number in column D.

There does not appear to be a mathematical pattern by which the number associated with each letter is determined, so it would be difficult to provide a means of calculating each number based on each letter (or letter pair).

What you have created in the first four columns is a pair of lookup tables in which you can look up a letter and find the corresponding number.

The lookup table can be removed from the six column table you have, and can be placed on a separate sheet if desired; it does not need to be attached to the 'working' table currently occupying columns F and G. The lookup formula will be simpler if the lookup table is reduced to two columns, rather than the four you are using.

In the example below, I have used the letter to number equivalents visible on your table, and arbitrarily assigned the number corresponding to each letter's position in the English language alphabet for letters not visible in your sample. I've included only the two two-letter combinations that are shown in your example.

Main is the equivalent of columns F and G of your table.

Lookup contains the data from columns A and B of your table, plus the current data from columns C and D of your table.

Lookup contains no formulas. All data is entered directly.

Main contains one formula, entered in cell B2, then filled down to the end of column B.

B2: =IF(LEN(A)<1,"",INDEX(Lookup::B,MATCH(A2,Lookup::A,0)))

The core formula that does the lookup is shown in bold. The core is entered as the if-false part of an IF statement that tests for an entry in column A by measuring the LENgth (in number of characters) of what is displayed in column A. If the length is less than one character, IF returns a null string, and exits the formula. If there is on or more character in A, IF calls the INDEX formula.

In the INDEX formula, MATCH looks in column A of Lookup for the value in A2 of Main. If it finds a match, it returns a number indicating the position of the matching item in the list in column A, and passes it on to INDEX.

INDEX counts that many items down the list in column B and returns the value it finds at that position.

Note the error triangle in the third last row of Main. This is flagging a 'can't find' error thrown by MATCH when it could not find the value "AA" in the search area, column A of Lookup.

Regards,

Barry

Hi Mark,

"I created a Lookup and also a Worksheet."

(image and quote from your earlier post)

You have created a Sheet named "Worksheet", a Sheet named "Lookup" and a Sheet named "Output".

Each of these Sheets contains at least one Table, and unless you have renamed those tables, each of them is named Table 1.

In the formula above, MATCH gets the value from cell A2 (of 'this table') and searches for the same value in column B of the Table named "Lookup". But there is no Table name "Lookup", so MATCH returns an error, producing the red triangle, containing the error message "There is a syntax error."

Change the lookup table's name to "Lookup" (without the quotes), and the formula should work as written. You may have to reenter the formula in B2.

Alternately,

Edit the formula to use the full addresses of the columns referenced by MATCH and INDEX to fit the current names of the sheet and table holding the lookup information, then after the formula is working, change the name of the lookup table to "Lookup". Numbers will then change the Table name in the formula and drop the Sheet name as it no longer needs it to find the correct columns.

Here's the edit needed:

Current:

IF(LEN(A)<1,"",INDEX(Lookup::B,MATCH(A2,Lookup::A,0)))

After edit:

IF(LEN(A)<1,"",INDEX(Lookup::Table 1::B,MATCH(A2,Lookup::Table 1::A,0)))

Regards,

Barry

Aha! Thank you for posting screenshots, helpful for diagnosing the issue.

Apologies, I wasn't prescriptive enough before. Do not put "Column" as I stated before. Actually click on the column you want. So, if you want to just copy the formula below, this will work:

=INDEX($B, MATCH(CELLREF, $A))

where CELLREF = the cell that has the letters you want to know the number for. This will be the only manual part you'll have to do.

Hi Mark,

Screen shots are indeed useful!

These are close ups of the two you last posted:

The tokens (labeled search-where, matching method, etc.) are cues, inserted by numbers to tell you what information to put at these points in the formula. The more important part is the punctuation.

The red arrow points to a comma, used in the formulas provided by To_Me, by me, and now by Paul, to separate the parts (arguments) of each function.

The green arrow points to a semi colon, inserted by Numbers to do the same job.

Apparently you live in a region where the comma ( , ) is used as the decimal separator ( as in €12,34 ). In these regions, the list separator in functions is the semi colon ( ; ), and you should be using that in place of the commas.

Changes to be made here:

Delete "COLUMN " leaving the B.

Delete "COLUMN " leaving the A.

Delete the closing parenthesis following F1

Change each comma to a semi colon ( ; )

Similar changes (comma to semi colon) will be needed in my formula and in To_Me's to make them work in your region.

Regards,

Barry

markusvdw wrote:

… but I don't get exact match... Numbers only allow me to input EXACT...

You can enter the value "false" ( no quotes ) or 0 for the "exact match", or use black triangle to select "exact match" when the function template comes up.

# Link to function reference : http://help.apple.com/functions/mac/7.0/#/ffa5997050

So, the formula to type in will be

=IFERROR( VLOOKUP( F, A:B, 2, false ), IFERROR( VLOOKUP( F, C:D, 2, false ), "" ) )

For the case of markusdw's tables,

=IFERROR( VLOOKUP( A, Lookup::A:B, 2, false ) , "" )

… I also get ; instead of , between the bits of information.

I guess it's ok as it is due to the language setting, in some case "," (comma) is used for decimal point.

# My setting uses "." (period) for decimal point.

How about like this ?

• use column B if it is on column A
• use column D if it is on column C
• otherwise blank

Hi Mark,

If you are in a region where the comma ( , ) is used as a decimal separator, then the semi colon ( ; ) is the separator used between 'bits of information' in functions.

VLOOKUP, used in To_Mi's formula can be set to accept either a "close match" or an "exact match". If you've built the formula by inserting the function from the Function Browser, you'll get the tokens, and can choose by clicking the black triangle toward the right end of the token.

MATCH offers three choices 'largest', which is the same as 'close match' and chooses the 'largest value equal to or less than' the search value, 'smallest', which is a close match in the other direction—the 'smallest value equal to or greater than' the search value, or 'find value' which accepts only an exact match. The zero in my MATCH function sets it to 'find value' and accepts only an exact match.

Regards,

Barry

Hey Mark,

I hate to introduce a different way of doing this since you've already started down a path, but I believe this can be simplified a bit.

If you have your "codex" (letters in one column, and their associated values in the adjacent column), then you can use a simple Index-Match formula, with no worry on errors or non-exact matches.

Index-Match works like this:

INDEX(Final Value you want to show in cell, Match(cell containing item to be looked up, Column containing those items))

SO, if Column A has all your letters, and Column B has all the respective values, your formula for trying to match the value with random letters would look as thus:

=INDEX(Column B, MATCH((Cell containing letter), Column A))

Then just paste all the way down. Please note, if you'd like to not show your codex, you'll need to hide those columns after you've input the formula.

Best,

Paul