How to LOOKUP with two columns?

[AlexB123]

I have been working on a data entry tool for an already existing workbook. I have to use the information available to return a row reference for my tool to work. The problem is there are no column headers. How can I write an index-match or lookup function to accept data from two consecutive columns to return desired value?

i.e., Suppose the following data:

a c 1
a d 2
b c 3
b d 4

If "a" in column 1 and "c" in column 2, i need a function to return "1"?

 

[Domenic]

Let's assume that A2:C5 contains the data, E2 contains the value of interest for Column A, and F2 contains the value of interest for Column B, try...

=INDEX($C$2:$C$5,MATCH(1,IF($A$2:$A$5=E2,IF($B$2:$B$5=F2,1)),0))

...which needs to be confirmed with CONTROL+SHIFT+ENTER.

Hope this helps!

 

[martindwilson]

two ways(one for the purists who insist array entered formulas are always the way to go)
Sheet1

*	A	B	C	D	E	F
a	c	*	a	d
a	d	xxxxx	*	*	*
b	c	*	*	*
b	d	*	*	*
result	*	*	*	*	*
xxxxx	*	i used "-" *as a seperator or else	*	*	*
or array formula	*	11&1	or ab&c	*	*
xxxxx	*	would be the same as	*	*	*
*	*	1&11	a&bc	*	*
*	*	abc	*	*

<colgroup><col style="font-weight:bold; width:30px; "><col style="width:64px;"><col style="width:64px;"><col style="width:205px;"><col style="width:64px;"><col style="width:64px;"><col style="width:64px;"></colgroup><tbody>
[TD="bgcolor: #cacaca, align: center"]1[/TD]

[TD="align: right"]1[/TD]

[TD="bgcolor: #cacaca, align: center"]2[/TD]

[TD="bgcolor: #cacaca, align: center"]3[/TD]

[TD="align: right"]3[/TD]

[TD="bgcolor: #cacaca, align: center"]4[/TD]

[TD="align: right"]4[/TD]

[TD="bgcolor: #cacaca, align: center"]5[/TD]

[TD="bgcolor: #cacaca, align: center"]6[/TD]

[TD="bgcolor: #cacaca, align: center"]7[/TD]

[TD="bgcolor: #cacaca, align: center"]8[/TD]

[TD="bgcolor: #cacaca, align: center"]9[/TD]

[TD="bgcolor: #cacaca, align: center"]10[/TD]

[TD="align: left"]111[/TD]

</tbody>

Spreadsheet Formulas

Cell Formula A6 =INDEX($C$1:$C$4,MATCH($E1&"-"&$F1,INDEX($A$1:$A$4&"-"&$B$1:$B$4,0),0)) A8 {=INDEX($C$1:$C$4,MATCH($E1&"-"&$F1,$A$1:$A$4&"-"&$B$1:$B$4,0))} <tbody> </tbody>

Formula Array: Produce enclosing { } by entering formula with CTRL+SHIFT+ENTER!

<tbody>
</tbody>

Excel tables to the web >> Excel Jeanie HTML 4

 

[Smitty]

Welcome to the Board!

Another way is to use VLOOKUP with a helper column in column A with this: =B1&C1

Excel 2012
	A	B	C	D	E	F	G
1	ac	a	c	1		ac	1
2	ad	a	d	2
3	bc	b	c	3
4	bd	b	d	4
Sheet1

Cell Formulas
Range	Formula
G1	=VLOOKUP(F1,A1:D4,4,FALSE)
A1	=B1&C1
A2	=B2&C2
A3	=B3&C3
A4	=B4&C4

HTH,

 

[yesterdays]

[TABLE="class: grid, width: 512, align: center"]
<colgroup><col width="64" span="8" style="width:48pt"> </colgroup><tbody>[TR]
[TD="width: 64, align: center"][/TD]
[TD="width: 64, align: center"]A[/TD]
[TD="width: 64, align: center"]B[/TD]
[TD="width: 64, align: center"]C[/TD]
[TD="width: 64, align: center"]D[/TD]
[TD="width: 64, align: center"]E[/TD]
[TD="width: 64, align: center"]F[/TD]
[TD="width: 64, align: center"]G[/TD]
[TD="width: 64, align: center"]H[/TD]
[/TR]
[TR]
[TD="width: 64, align: center"]1[/TD]
[TD="width: 64, align: center"]a[/TD]
[TD="width: 64, align: center"]c[/TD]
[TD="width: 64, align: center"]1[/TD]
[TD="width: 64, align: center"][/TD]
[TD="width: 64, align: center"]Lookup[/TD]
[TD="width: 64, align: center"]a[/TD]
[TD="width: 64, align: center"]c[/TD]
[TD="width: 64, align: center"]1[/TD]
[/TR]
[TR]
[TD="align: center"]2[/TD]
[TD="align: center"]a[/TD]
[TD="align: center"]d[/TD]
[TD="align: center"]2[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[/TR]
[TR]
[TD="align: center"]3[/TD]
[TD="align: center"]b[/TD]
[TD="align: center"]c[/TD]
[TD="align: center"]3[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[/TR]
[TR]
[TD="align: center"]4[/TD]
[TD="align: center"]b[/TD]
[TD="align: center"]d[/TD]
[TD="align: center"]4[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

H1: =INDEX($C$1:$C$4,MATCH(1,($A$1:$A$4=F1)*($B$1:$B$4=G1),0))
Ctrl Shift Enter

 

[AlexB123]

Can I use one of these array formulas with VBA? Would "Evaluate" or ".FormulaArray" ensure the calculation is done?

 

[martindwilson]

bangs head on wall..why did you not mention this?

 

[AlexB123]

bangs head on wall..why did you not mention this?

I have another thread ... I'm building a UserForm, and the last part to figure out is this piece. Trying to break the problem down into simple parts?