Is there an equivalent of "Match" function in Excel?

[MathieuLap]

Hi,

I'm sorry, english isn't my mother tongue, so I tried to look in the forum to answer my question, but did'nt find...

I have a large array (60 columns X 100 lines) first line is the questions, and all the following datas in the same column is the answer. Let's say that a question is in column 3, Then I do an _ArrayExctrat from column 3 and give a new name to that array related to the question. But in the future, the questions (and the following answer below) might move and be in column 5 or 10. So if I do an array extract of column 3 it won't work anymore.

I found a way using while loop (while the name of the column is not what I'm looking for, grow a iteration by 1 and then this iteration is the number of my column) 

But making a while loop for all my 60 questions is long to do and takes a lot of line code!

Is there a way to be more efficient? In Excel, there is this MATCH function : 

           A                     B                   C

1   Fisrt name   Last name   date of birth

2    Mathieu       Laplante        may, 4th 1980

3    Ginette         Paré              oct, 10th, 1974

 

=match("Last Name";A1:C1) wil give me 2 as an answer...

 

Thanks a lot!!

 

 

 

 

 

 

[Nine]

Take a look at _ArraySearch in help file.  Seems to me that this is what you want.

Edited February 1, 2020 by Nine

[iamtheky]

The loop is probably most effective and it does not "take a lot of line of code" (it probably takes less lines than my example).  That aside, pulling this off without a loop sounded like fun

this assumes you have read your excel file to an array that looks like the one in the OP:

#include<array.au3>

local $a[3][4] = [["1" , "First name" , "Last name" , "date of birth"],["2", "Mathieu" , "Laplante" , "may, 4th 1980"],["3" ,  "Ginette" ,  "Pare" , "oct, 10th, 1974"]]

_ArrayTranspose($a)
msgbox(0 , '' , _ArraySearch($a , "Last name"))

 

edit:  wait wth, you only need the column that appears in (or do you need need row and column like a jeopardy board)?  How did you know it was in row 1 to search a1-c1? 

 

Edited February 1, 2020 by iamtheky

[jchd]

Another, more efficient way: create two arrays.

The first lists the questions and their index in the data array column number.

[MathieuLap]

Amazing !!!  _ArraySearch AND _ArrayTranspose before!!!!!

 

Thank you so much!!!

 

But tell me... How would you have used while loop and be more efficient? Cause I was planning to do this code for every single questions... (40 times!) $aRetArray is my main array, $Question is the first line of this array. Once $iteration is on the question "You Are :", I get my column number...

$iteration = 0

while $Question[0][$iteration] <> "You are : "
    $iteration = $iteration+1
WEnd
Global $ResidentStatus = _arrayextract($aretarray,0,100,$iteration,$iteration)
$iteration = 0

 

 

[iamtheky]

I dont know if this is a legit usage of exitloop (that since I dont care about the level i am exiting just doing something else there instead).

but this is the quickest I can play the match game

#include<array.au3>

local $a[3][4] = [["1" , "First name" , "Last name" , "date of birth"],["2", "Mathieu" , "Laplante" , "may, 4th 1980"],["3" ,  "Ginette" ,  "Pare" , "oct, 10th, 1974"]]

;~ $sSearch= "2"
;~ $sSearch = "Mathieu"
;~ $sSearch = "Pare"
$sSearch = "nomatch"

$result = "not found"

for $i = 0 to ubound($a) - 1
If _ArraySearch($a , $sSearch , 0 , 0 , 0 , 0 , 0, $i) > -1 then exitloop(assign("result" , "$a[" & _ArraySearch($a , $sSearch , 0 , 0 , 0, 0, $i) & "][" & $i & "]"))
next

msgbox(0, '' , $result)