two-column lookup

[Guest]

A WS has names in col A and multiple blocks with the same name(s) with the
corresponding values in subsequent cols. Here is an example
A B C D
------------
1 a a 1 x match a & 4, should give z
2 b a 2 y
3 c a 4 z
4
5 d b 1 k match b & 4, should give m
6 e b 3 l
7 f b 4 m
(the value 4 in col C is the second condition to be matched).
For each value in col A, I'd like to find values in col D _IF_ the values in
cols B and C also match my conditions.

I've tried to build INDEX/MATCH/* formulas, but so far with little success
as the dual syntax of INDEX is 'a bit' confusing. Chip Pearson's Lookups page
was helpful, but did not have such a case. Bob Phillips' formula, while
relevant, is inscrutable to me:
"If by chance you mean a double lookup, where you have 2 key columns and you
want to match both and get the adjacent value in another column, you can use
=INDEX(C1:C100, MATCH(D1&D2,A1:A100&B1:B100,0))"--what is what here?

z.entropic

 

[Biff]

Hi!

Here are the formulas that will do what you want:

Entered with the key combo of CTRL,SHIFT,ENTER:

match a & 4, should give z
=INDEX(D17,MATCH("A4",B1:B7&C1:C7,0))

match b & 4, should give m

=INDEX(D17,MATCH("B4",B1:B7&C1:C7,0))

What these (and Bob's example) do is to concatenate the lookup values "A"
and "4" and to also concatenate the lookup range "B1" and "C1". It would
look like this:

Lookup value: A4

Lookup range:

A1
A2
A4

B1
B3
B4

However, I can't see a correlation with these formulas and where it appears
that you want to place the formulas.

Based on your posted sample, the formulas look like they are in cells E1 and
E5.

Biff

 

[Guest]

Sorry, now I see I wasn't clear enough. Here are a few more details:
1. The number "4", or any other, is a constant that could either be entered
into a formula directly, or its value copied from a separate single cell,
2. The blocks of data extend down for hundreds or thousands of lines,
3. the array formula would be in a column inserted between cols A and B and
copied by dragging down the first entered cell.
4. importantly, by 'match & 4' I really meant MATCH values 'b' and '4' from
the same row, but in different columns'.

Hope this clarifies my question.

z.entropic

 

[Guest]

I re-wrote my example to clarify it even more:
A B C D E
1 4
2 a a 1 o
3 b a 2 p
4 c a 3 q
5 d a 4 r
6 e
7 f b 2 s
8 g b 4 t
9 h b 3 u
10 i b 1 v
11 j
12 k c 5 x
13 l c 1 y
14 m c 2 z
15 n c 4 k

In effect, the formula in B2 should give 'r', because C2=A2 AND D5=$B$1 (a
VLOOKUP in two columns at the same time, where one value (B1) is a constant).
The value obtained in B3 by dragging B2 should be 't' (C7=A3 AND $B$1=4).

z.entropic

 

[Biff]

Hi!

3. the array formula would be in a column inserted between cols A and B and
copied by dragging down the first entered >cell.

If that's the case then your data is in the range C:F

Entered in B2 as an array with the key combo of CTRL,SHIFT,ENTER:

=INDEX(F$2:F$15,MATCH(C2&B$1,D$2$15&E$2:E$15,0))

Copied down returns:

B2 = R
B3 = T
B4 = K
B5:B15 = #N/A

If you want to suppress the display of #N/A:

=IF(ISNA(MATCH(C2&B$1,D$2$15&E$2:E$15,0)),"",INDEX(F$2:F$15,MATCH(C2&B$1,D$2$15&E$2:E$15,0)))

OR, use the shorter formula together with conditional formatting:

Select the range B2:B15
Conditional Formatting
Formula is: =ISNA(B2)
Set the font color to be the same as the background color.
OK out

Biff

 

[Guest]

Hi, Biff--thanks for your help and effort, but we're still not there...
Where does the formula use col A where the reference string is?

If you look at my last example, I need to take cell A2, find the same
strings in block C2:C500, then take cell B$1, search col D for values equal
to B$1 and match BOTH A2 and B$1 in the same row, in which the sought value
will be in col E. There is no col F.

z.entropic

 

[Guest]

Biff, finally and with your valuable guidance I got the syntax right; it's
actually simple! Thanks a lot for your time.

The solution for my latest example (maybe the spacing got screwed up in my
post) is
=INDEX(E$2:E$15,MATCH(A2&B$1,C$2:C$15&D$2$15,0))

z.entropic

 

[Biff]

it's actually simple!

maybe the spacing got screwed up in my post

Well, not the spacing. I read the table as column A having the values:
1,2,3, 4 etc. when they were actually the row numbers. Doh! But, that
happens.

Anyhow, glad you got it to work.

Biff