Lookup - 2 dimensional

[Guest]

I'm trying to lookup a value from sheet 2, from given values in sheet 1 of
one excel workbook.

Sheet 1 inputs into the formula:
a=1234
b=123456

Sheet 2 looks like this:

1234 999999 45
1234 888888 33
1234 123456 36
2345 123456 99
2345 666666 88
2345 999999 77

I would like for it to return the value 36, given the 1234 and 123456 values.

Thanks!

 

[Dave Peterson]

=index(c1:c6,match(1,(a1:a6=1234)*(b1:b6=123456),0))

This is an array formula. Hit ctrl-shift-enter instead of enter. If you do it
correctly, excel will wrap curly brackets {} around your formula. (don't type
them yourself.)

 

[hgrove]

CS wrote...

I'm trying to lookup a value from sheet 2, from given values in
sheet 1 of one excel workbook.

Sheet 1 inputs into the formula:
a=1234
b=123456

Sheet 2 looks like this:

1234 999999 45
1234 888888 33
1234 123456 36
2345 123456 99
2345 666666 88
2345 999999 77

I would like for it to return the value 36, given the 1234 and >12345

values.

The most reliable way to do this involves concatenating your searc
values and the columns against which you're trying to match them. I
your sheet 1 values were entered in cells named a and b, and if you
table in sheet 2 were named TBL, you could use the *array* formula

=INDEX(TBL,MATCH(a&CHAR(127)&b,INDEX(TBL,0,1)&CHAR(127)
&INDEX(TBL,0,2),0),3)

Note: enter array formulas by holding down [Ctrl] and [Shift] key
before pressing [Enter] (or hold down [Cmd] before pressing [Return] o
Macs)

 

[RagDyer]

Assume A1 and B1 on Sheet1 contain given values.

Formula is on Sheet2 with data list:

=INDEX(C1:C6,MATCH(Sheet1!A1&Sheet1!B1,A1:A6&B1:B6,0))
--

HTH,

RD
==============================================
Please keep all correspondence within the Group, so all may benefit!
==============================================


I'm trying to lookup a value from sheet 2, from given values in sheet 1 of
one excel workbook.

Sheet 1 inputs into the formula:
a=1234
b=123456

Sheet 2 looks like this:

1234 999999 45
1234 888888 33
1234 123456 36
2345 123456 99
2345 666666 88
2345 999999 77

I would like for it to return the value 36, given the 1234 and 123456
values.

Thanks!

 

[Harlan Grove]

Assume A1 and B1 on Sheet1 contain given values.

Formula is on Sheet2 with data list:

=INDEX(C1:C6,MATCH(Sheet1!A1&Sheet1!B1,A1:A6&B1:B6,0))

....

Dave's approach is best. Your approach is dangerous if columns A and B could
contain arbitrary strings or numbers.

123 4567 foo
1234 567 bar

 

[RagDyeR]

Isn't that always the case when the criteria is *not* hard coded into the
formula?

I don't relish the fact of users (mine anyway) changing formulas to change
parameters.
--

Regards,

RD
--------------------------------------------------------------------
Please keep all correspondence within the Group, so all may benefit !
--------------------------------------------------------------------

Assume A1 and B1 on Sheet1 contain given values.

Formula is on Sheet2 with data list:

=INDEX(C1:C6,MATCH(Sheet1!A1&Sheet1!B1,A1:A6&B1:B6,0))

....

Dave's approach is best. Your approach is dangerous if columns A and B could
contain arbitrary strings or numbers.

123 4567 foo
1234 567 bar

 

[Guest]

This worked! Thankyou!!!

Now a dumb question - it seems to work in one cell, but I have problems
using it in other cells. I keep getting a #Name? even though I am SURE the
references (i.e. changing the 1234 reference to the 3456 reference) are
correct. Is there something I don't know about working with arrays formulas?

 

[Frank Kabel]

Hi
post your exact formula

 

[Dave Peterson]

Yeah but...

=index(c1:c6,match(1,(a1:a6=1234)*(b1:b6=123456),0))
could be rewritten to:
=index(c1:c6,match(1,(a1:a6=z7)*(b1:b6=z8),0))

where z7 and z8 contain the values.

 

[Dave Peterson]

On top of posting your formula, post what you had in the cells.

If you had 1234 formatted as text, then the formula would have to change:

Kind of:
=index(c1:c6,match(1,(a1:a6="1234")*(b1:b6=123456),0))

 

[RagDyer]

I realize that, and I'm sure Harlan does also!
So then, please tell me the difference between our two formulas when your
re-written version has the cell references, as mine has.
--


Regards,

RD
--------------------------------------------------------------------
Please keep all correspondence within the Group, so all may benefit!
-------------------------------------------------------------------

Yeah but...

=index(c1:c6,match(1,(a1:a6=1234)*(b1:b6=123456),0))
could be rewritten to:
=index(c1:c6,match(1,(a1:a6=z7)*(b1:b6=z8),0))

where z7 and z8 contain the values.

 

[Dave Peterson]

I think Harlan's example showed what could happen when you concatenate the values:

123 4567 foo
1234 567 bar

 

[Domenic]

I think Harlan's example showed what could happen when you concatenate the
values:

123 4567 foo
1234 567 bar

If you're going to concatenate, then the following formula should keep
errors in check:

=INDEX(C1:C6,MATCH(Sheet1!A1&CHAR(127)&Sheet1!B1,A1:A6&CHAR(127)&B1:B6,0)
)

entered using CONTROL+SHIFT+ENTER

 

[Frank Kabel]

Hi
try formating cell B4 as 'Text' and re-enter your value or enter the
following in B4:
'280277
note the preceding apostrophe

 

[RagDyeR]

"Now I see", said the blind man!<g>
--

Regards,

RD
--------------------------------------------------------------------
Please keep all correspondence within the Group, so all may benefit !
--------------------------------------------------------------------

I think Harlan's example showed what could happen when you concatenate the
values:

123 4567 foo
1234 567 bar

 

[Dave Peterson]

"Now I see," said the blind man as he picked up the hammer and saw!
<vbg>

 

[Guest]

"Now I see," said the blind man to his deaf daughter as he picked up the
hammer and saw!