14 Replies Latest reply on Jun 21, 2017 7:46 AM by philmodjunk

# Index and Match in Excel is = to "What?" in FM10

Hi,

I'm new to FM and use a rather old version (FM Pro 10 Advanced). I have to convert an Excel table that uses this formula: "INDEX(A1:A5,MATCH(MAX(B1:B5),B1:B5,0)). This formula returns the max value from the column B1:B5, matches the row where this max value is with the column A1:A5 and returns the value from the column A1:A5, which is on the same row.

I have googled this issue but could not find any hint. Could someone help me with this

thank you very much in advance
Nicolas

• ###### 1. Re: Index and Match in Excel is = to "What?" in FM10

You need different thinking in database, from spreadsheet.

There is no "range of row" in database. Does your Excel table have only total 5 rows?

1 of 1 people found this helpful
• ###### 2. Re: Index and Match in Excel is = to "What?" in FM10

Index and match are described here:

I'm not an Excel-guy, but AFAIK, it basically gets a value from a range of cells depending on a search-value (as shown in the examples above)

- easy with sql (not available in FM10 without plugins)

- in FM10, You can get the values per 'list' function for a specific field in a found set

there must be easier methods, but walking through could be like this

- search for desired records (thats the range of cells in Excel).

- say, we have records with field1 and field2 (two columns in excel)

- build a list of the values of field1 using the list-function

- use a custom function for the position of a given criteria in list(field1)

- build a second list with field2

- to get the corresponding value in the second list, use getvalue(list2;position)

there are several libraries for custom function, ie briandunning.com

here's a function that gives back the position in a list for a specific value

• ###### 3. Re: Index and Match in Excel is = to "What?" in FM10

FileMaker is not a spreadsheet and that difference is significant.

Assuming that each row row is a record and each column is a field, it sounds like you want the value of field A from the record with the largest value in field B.

If I have that right, ExecuteSQL could be used to return that value;

SELECT A FROM table

ORDER BY B

FETCH FIRST 1 ROW ONLY

You can also use a script to sort records on B, then access the value of A from the first record.

• ###### 4. Re: Index and Match in Excel is = to "What?" in FM10

yes you are right (my brain is still thinking in spreadsheet mode!!). My table has actually 10 rows

• ###### 5. Re: Index and Match in Excel is = to "What?" in FM10

INDEX using MATCH is a more flexible approach to and more powerful than the VLOOKUP and HLOOKUP found in Excel. VLOOKUP by way of example looks for a selected value in the leftmost column of the defined range to return the value in the (stated) n columns to the right, along with some other qualifiers to ensure a perfect match or allow a first/closest match.

The key here is “to the right” [for HLOOKUP read rows down]. If your return value is in a column to the left (above for HLOOKUP) then it cannot work so you need to rearrange or add or repeat with an extra column that is left of the desired return value column.

INDEX using MATCH does not suffer from the same restriction.

When you want more than powerful number crunching from Excel, Filemaker is now my preferred choice.

• ###### 6. Re: Index and Match in Excel is = to "What?" in FM10

I think what you are doing is looking up the maximum value of a field in order to use that value to find a record in another table and obtain a field content from that record. For example: what is the largest account number used on this journal; 123; now get me the account description for account number 123.

You could consider adding a summary field to the table to be the maximum value of the records in the field. [I have learned that although the idea of adding a new field for such a purpose does seem at first very odd – you get used to it and it does have its benefits.] This may then open up possibilities to use that summary field, may be with LOOKUP or as a foreign key to another related table.

The SQLExecute route suggested by  philmodjunk  may still be a simpler option.

nico357 wrote:

yes you are right (my brain is still thinking in spreadsheet mode!!). My table has actually 10 rows

Not sure the number of records is relevant, though.

EDIT

The ExecuteSQL route suggested by  philmodjunk  may still be a simpler option.

• ###### 7. Re: Index and Match in Excel is = to "What?" in FM10

nico is running FileMaker 10 - no ExecuteSQL

there are plugins available that can help - but he didn't mention any plugins

-> therefore: What should be achieved? Then, pick a FileMaker method - as user19752 mentioned

• ###### 8. Re: Index and Match in Excel is = to "What?" in FM10

Markus Schneider wrote:

nico is running FileMaker 10 - no ExecuteSQL

there are plugins available that can help - but he didn't mention any plugins

-> therefore: What should be achieved? Then, pick a FileMaker method - as user19752 mentioned

The help menus suggest ExecuteSQL came in version 6 or earlier.

http://www.filemaker.com/help/12/fmp/html/scripts_ref2.37.69.html#1029790

I only have v15, so cannot check. But as you say pick a method from those available.

• ###### 9. Re: Index and Match in Excel is = to "What?" in FM10

piaccounting wrote:

Markus Schneider wrote:

nico is running FileMaker 10 - no ExecuteSQL

there are plugins available that can help - but he didn't mention any plugins

-> therefore: What should be achieved? Then, pick a FileMaker method - as user19752 mentioned

The help menus suggest ExecuteSQL came in version 6 or earlier.

http://www.filemaker.com/help/12/fmp/html/scripts_ref2.37.69.html#1029790

I only have v15, so cannot check. But as you say pick a method from those available.

That's the "Execute SQL" script step to push data into an ODBC connection.

The "ExecuteSQL()" function is what is mentioned in this thread.

1 of 1 people found this helpful
• ###### 10. Re: Index and Match in Excel is = to "What?" in FM10

Check that help again!

Execute SQL

Is a script step to use the statements with an external SQL/ODBC source.

INSERT, UPDATE, or DELETE.

(The Import script step is used to SELECT, btw.)

This is confused with the function

ExecuteSQL()

(note spelling difference)

that first was available in FM12.

Sent from miPhone

1 of 1 people found this helpful
• ###### 11. Re: Index and Match in Excel is = to "What?" in FM10

Thanks for the clarity. Still on the learning curve...

Dave

• ###### 12. Re: Index and Match in Excel is = to "What?" in FM10

Thanks for the clarity.

• ###### 13. Re: Index and Match in Excel is = to "What?" in FM10

apologies for my mistake.

Dave

• ###### 14. Re: Index and Match in Excel is = to "What?" in FM10

I did forget that 10 doesn't have the ExecuteSQL function, but note that I also pointed out a scripted alternative that produces the same result. That option will work with version 10.