array match
 

This really has me stumped. Any help will be appreciated.

My data (partial):

col a col b col c
315 91 permit reqd
50 168 license reqd
91 172 regulatory reqd

notes: col a is a 15 row by 1 col range on sheet1 named "section" and will
not always be in ascending order, col b and col c are on sheet2. col b/col c
is a 13 row by 2 col range named "permit".

what I want to do: in cell sheet1!d5 put a formula that will return from the
permit range "permit reqd" IF 91 is entered or "license reqd" if 168 is
entered etc in the col a range, otherwise cell d5 would be blank.

For now, the numbers in col a and col b are entered as text. If that is an
issue, I can change them to numeric.

That leads to another question...

In researching this, and specifically using MATCH in an array formula, I've
noticed the syntax for an array formula using MATCH:
=MATCH(1,(condition1)*(condition2),0). What is the significance of the "1"
after the opening parenthesis?

TIA

array match
 

If "condition1" is TRUE, and "condition2" is TRUE
TRUE*TRUE=1

=MATCH(1,(TRUE)*(TRUE),0)

simplify
=MATCH(1,1,0)



"Mike S" wrote:

This really has me stumped. Any help will be appreciated.

My data (partial):

col a col b col c
315 91 permit reqd
50 168 license reqd
91 172 regulatory reqd

notes: col a is a 15 row by 1 col range on sheet1 named "section" and will
not always be in ascending order, col b and col c are on sheet2. col b/col c
is a 13 row by 2 col range named "permit".

what I want to do: in cell sheet1!d5 put a formula that will return from the
permit range "permit reqd" IF 91 is entered or "license reqd" if 168 is
entered etc in the col a range, otherwise cell d5 would be blank.

For now, the numbers in col a and col b are entered as text. If that is an
issue, I can change them to numeric.

That leads to another question...

In researching this, and specifically using MATCH in an array formula, I've
noticed the syntax for an array formula using MATCH:
=MATCH(1,(condition1)*(condition2),0). What is the significance of the "1"
after the opening parenthesis?

TIA

array match
 

From my reading, this should suffice & work for you ..

In Sheet1,
Assuming lookup numbers (91, 172, 168, etc) will be input as text numbers in
A1 down, place in B1:
=IF(A1="","",IF(ISNA(VLOOKUP(A1+0,permit,2,0)),"", VLOOKUP(A1+0,permit,2,0)))
Copy down as far as required

=MATCH(1,(condition1)*(condition2),0).
What is the significance of the "1" after the opening parenthesis?

(condition1)*(condition2) will return an array of 1's/0's, eg: {0,0,1,0,1,0}
depending on whether both conditions are satisfied or not.

The "1" is the lookup value to exact match with the first "1" within the
array. MATCH will then return the position within the array.

For the example array: =MATCH(1,(condition1)*(condition2),0)
resolves to: = MATCH(1,{0,0,1,0,1,0},0)
and then to: 3
since the "1" finds an exact match in position 3 within the array:
{0,0,1,0,1,0}
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"Mike S" wrote:
This really has me stumped. Any help will be appreciated.

My data (partial):

col a col b col c
315 91 permit reqd
50 168 license reqd
91 172 regulatory reqd

notes: col a is a 15 row by 1 col range on sheet1 named "section" and will
not always be in ascending order, col b and col c are on sheet2. col b/col c
is a 13 row by 2 col range named "permit".

what I want to do: in cell sheet1!d5 put a formula that will return from the
permit range "permit reqd" IF 91 is entered or "license reqd" if 168 is
entered etc in the col a range, otherwise cell d5 would be blank.

For now, the numbers in col a and col b are entered as text. If that is an
issue, I can change them to numeric.

That leads to another question...

In researching this, and specifically using MATCH in an array formula, I've
noticed the syntax for an array formula using MATCH:
=MATCH(1,(condition1)*(condition2),0). What is the significance of the "1"
after the opening parenthesis?

TIA

array match
 

Thanks for your quick reply Max, but that's not quite what I need. I'm
looking for a single formula to put in cell d5 that will return the 2nd
column value in "permit" if one of the values in the first column of "permit"
is entered in "section". For now, let's assume only one of the values in the
first column of "permit" will be entered in "section".

I guess it's going to take, maybe, a combo array formula using index/match...



"Max" wrote:

From my reading, this should suffice & work for you ..

In Sheet1,
Assuming lookup numbers (91, 172, 168, etc) will be input as text numbers in
A1 down, place in B1:
=IF(A1="","",IF(ISNA(VLOOKUP(A1+0,permit,2,0)),"", VLOOKUP(A1+0,permit,2,0)))
Copy down as far as required

=MATCH(1,(condition1)*(condition2),0).
What is the significance of the "1" after the opening parenthesis?

(condition1)*(condition2) will return an array of 1's/0's, eg: {0,0,1,0,1,0}
depending on whether both conditions are satisfied or not.

The "1" is the lookup value to exact match with the first "1" within the
array. MATCH will then return the position within the array.

For the example array: =MATCH(1,(condition1)*(condition2),0)
resolves to: = MATCH(1,{0,0,1,0,1,0},0)
and then to: 3
since the "1" finds an exact match in position 3 within the array:
{0,0,1,0,1,0}
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"Mike S" wrote:
This really has me stumped. Any help will be appreciated.

My data (partial):

col a col b col c
315 91 permit reqd
50 168 license reqd
91 172 regulatory reqd

notes: col a is a 15 row by 1 col range on sheet1 named "section" and will
not always be in ascending order, col b and col c are on sheet2. col b/col c
is a 13 row by 2 col range named "permit".

what I want to do: in cell sheet1!d5 put a formula that will return from the
permit range "permit reqd" IF 91 is entered or "license reqd" if 168 is
entered etc in the col a range, otherwise cell d5 would be blank.

For now, the numbers in col a and col b are entered as text. If that is an
issue, I can change them to numeric.

That leads to another question...

In researching this, and specifically using MATCH in an array formula, I've
noticed the syntax for an array formula using MATCH:
=MATCH(1,(condition1)*(condition2),0). What is the significance of the "1"
after the opening parenthesis?

TIA

array match
 

Try in D5, array-entered (press CTRL+SHIFT+ENTER):
=IF(ISNA(MATCH(TRUE,ISNUMBER(MATCH(INDEX(permit,,1 ),section,0)),0)),"",INDEX(permit,MATCH(TRUE,ISNUM BER(MATCH(INDEX(permit,,1),section,0)),0),2))
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"Mike S" wrote:
Thanks for your quick reply Max, but that's not quite what I need. I'm
looking for a single formula to put in cell d5 that will return the 2nd
column value in "permit" if one of the values in the first column of "permit"
is entered in "section". For now, let's assume only one of the values in the
first column of "permit" will be entered in "section".

I guess it's going to take, maybe, a combo array formula using index/match...

array match
 

Fantastic!!! Worked perfectly. Thank you Max.

"Max" wrote:

Try in D5, array-entered (press CTRL+SHIFT+ENTER):
=IF(ISNA(MATCH(TRUE,ISNUMBER(MATCH(INDEX(permit,,1 ),section,0)),0)),"",INDEX(permit,MATCH(TRUE,ISNUM BER(MATCH(INDEX(permit,,1),section,0)),0),2))
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"Mike S" wrote:
Thanks for your quick reply Max, but that's not quite what I need. I'm
looking for a single formula to put in cell d5 that will return the 2nd
column value in "permit" if one of the values in the first column of "permit"
is entered in "section". For now, let's assume only one of the values in the
first column of "permit" will be entered in "section".

I guess it's going to take, maybe, a combo array formula using index/match...

array match
 

Good to hear that, Mike !
You're welcome.
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"Mike S" wrote in message
...
Fantastic!!! Worked perfectly. Thank you Max.