Lookup - 2 dimensional

  • Thread starter Thread starter Guest
  • Start date Start date
G

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!
 
=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.)
 
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
Click to expand...
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)
 
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!
 
RagDyer said:
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))
Click to expand...
....

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
 
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 !
--------------------------------------------------------------------

RagDyer said:
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))
Click to expand...
....

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
 
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?
 
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.
 
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))
 
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.
 
I think Harlan's example showed what could happen when you concatenate the values:

123 4567 foo
1234 567 bar
 
I think Harlan's example showed what could happen when you concatenate the
values:

123 4567 foo
1234 567 bar
Click to expand...

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
 
Hi
try formating cell B4 as 'Text' and re-enter your value or enter the
following in B4:
'280277
note the preceding apostrophe
 
"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
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Vlookup and retrieve values 1 row below the normally retrieved val 2
Merge 2 excel worksheets into one and remove duplicate entries. 1
Lookup matching two values 4
Offset/Match Double Lookup 4
Lookup Question 1
With F1 value, look at A, if match or partial match return B 5
join worksheets 2
Vook up help required 9

Back
Top