array match

[Mike S]

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

 

[Teethless mama]

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

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

simplify
=MATCH(1,1,0)

 

[Max]

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}

 

[Mike S]

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]

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,ISNUMBER(MATCH(INDEX(permit,,1),section,0)),0),2))

 

[Mike S]

Fantastic!!! Worked perfectly. Thank you Max.

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,ISNUMBER(MATCH(INDEX(permit,,1),section,0)),0),2))

 

[Max]

Good to hear that, Mike !
You're welcome.