Do you actually need to lookup each individual column or can you check and
array/table?

So my problem is this (using current cell references not what is in this
thread):

I have a table: C11:M47

In Columns D - G there is a list of names that I trying to find an exact
match with the value in C3. There could be multiple matches, so as a second
criteria I want to see if the value in C4 can be found (i.e. not an exact
match) in the column H11:H47.

If a double match is found, then I would like the row number or the row
within the table. With the row I will then select the data from various
columns in the table.

--
Trefor


"Trefor" wrote:

Max or anyone that can understand this,

The forumlae you did for me will give me a value in another column within
the array. Is it possible for the result to be the row within the array? That
way I calculate the row once and can then use the row number as a reference
to pull data from other columns in the array/table.

--
Trefor


"Max" wrote:

Welcome, glad it worked out ok for you.

.. not sure I understand what you have done
Here's some easy explanations to help:

Indicatively, the collapsed expression is simply this:
=IF(ISNA(1),IF(ISNA(2),IF(ISNA(3),"",INDEX(3)),IND EX(2)),INDEX(1)))

It's essentially a sequential, dual criteria index/match using the 2 inputs
that you have in C3 and C4. The matching sequence (read from left to right
in the collapsed expression) is: Match C3/C4 vs Cols D & H then vs Cols E
& H then vs Cols F & H. And where it matches the dual criteria (the first
matching instance), extract corresponding value from col C (C11:C14).

This part of it, eg:
(ISNUMBER(SEARCH(C3,D11:D14)))*(ISNUMBER(SEARCH(C4 ,H11:H14)
resolves to an array of 1's/0's depending on where the dual criteria** is
satisfied (1's) or not (0's), eg: {0;0;1;0}
**ie check where D11:D14 contains C3 AND H11:H14 contains C4

MATCH(1,{0;0;1;0},0) then returns the position of the 1st/single "1" within
the array, eg over he 3

This position: 3 is then used to extract the corresponding value from the
INDEX(C11:C14, ...), viz it'll return the 3rd element from C11:C14, ie
what's in C13
--
Max
Singapore
http://savefile.com/projects/236895
Downloads: 15,500, Files: 352, Subscribers: 53
xdemechanik
---
"Trefor" wrote in message
...
Max,

Looks good, not sure I understand what you have done, but this is a once
off
and my problem is fixed so I am happy ;)

Many thanks again.

--
Trefor