# matching a value in an array and returning row number

1. ## matching a value in an array and returning row number

Hello,

I have MATCH("abc",A:A,0), I want to change it to MATCH("abc",A:G,0), but match only likes looking in a single row/column and returning the corresponding column/row. I want to look at a whole array, A:G, and return the row position of the result. I feel like there should've been a single formula in excel, but I can't find anything in the lookup & reference section. I'm probably just being really dense right now. Oh, and this is part of a larger equation, so hopefully we can result in something no bigger than the "ideal" match equation i had above. Here's an ex in case you're further confused:
COLUMN
A....B....C....D....E
12....AS....FV....HT....JU
XE....4R....H7....QW....EW
LX....2Q....7U....2S....45

So, H7 is in C2, I want it to return 2.

2. ## Re: matching a value in an array and returning row number

With this formula it is advisable not to use whole column references.. used defined range:

e.g

=SUMPRODUCT((\$A\$1:\$G\$100="abc")*(ROW(\$A\$1:\$G100)-ROW(\$A\$1)+1))

3. ## Re: matching a value in an array and returning row number

Well, it works, but it confuses me a bit. Whats with the last part for? -row(\$A\$1)+1 ...doesn't this always come out to 0?

anyway, the final product of all the work is:
``Please Login or Register  to view this content.``
It looks in column B and finds a match in a seperate workbook denoted by R2, and basically does a combo of a vlookup and hlookup without knowing the leftmost column, hence the index and sumproduct now.

4. ## Re: matching a value in an array and returning row number

This part together:

ROW(\$A\$1:\$G100)-ROW(\$A\$1)+1)

forms an array of values (row numbers) from 1 to 100

The first part: (\$A\$1:\$G\$100="abc") returns an array of TRUEs and FALSEs (hopefully only 1 TRUE, where an actual match was found).

Each of these elements is multiplied by the row numbers array, and where the TRUE occurs, it is multiplied by the row number, therefore returning the row number itself.... all the FALSEs multiplied by the row numbers, result in 0... so the sum of all these results, will be the same as the lone TRUE row... and so the result is achieved.

You can use just ROW(\$A\$1:\$G100), but ROW(\$A\$1:\$G100)-ROW(\$A\$1)+1) is used for robustness, in cases that you may add/remove rows... then it maintains robustness.

There are currently 1 users browsing this thread. (0 members and 1 guests)

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1