Knowing me, knowing you, choose baby

[gopa]

a e 1
b e 2
c e 3
d e 4
a f 5
b f 6
c f 7
d f 8

Me a
You e
Baby ?

With me and you displayed in cells, I want to get right baby's number.

 

[Aladin Akyurek]

On 2002-10-30 00:05, gopa wrote:
PaddyD

It's OK, now I've spotted a few minor glitches(mine, not yours) and remembered to Ctrl+Shift+Enter EVERY time I edited!

This is real gen for the kind of things I am often doing, many, many thanks.

HughJ

Hugh,

If you're going to copy that array-formula to many cells, you're going to suffer a performance problem. You might want to look for a non-array concatenation solution. Do a search at this site using "concatenation" and "multikey" as key words.

Here is a link...

http://www.mrexcel.com/board/viewtopic.php?topic=22767&forum=2

with an Addendum for the non-array approach.

Aladin
This message was edited by Aladin Akyurek on 2002-10-30 00:18

 

[IML]

On 2002-10-30 00:05, Aladin Akyurek wrote:

On 2002-10-29 23:50, IML wrote:
what about a good, old fashion sumproduct?

[...]

Ian,

That's dangerous. change A8 to "a" and try Me=a and You=f.

Aladin

Good point, but what is the right answer here? 5, 8 or 13. I was (rightly or wrongly) assuming only unique combinations.

Playing with Colo's toy, I added your vlookup option (I will try not to get carried away with this)

Book2
	A	B	C	D	E
1	a	e	ae	1
2	b	e	be	2
3	c	e	ce	3
4	d	e	de	4
5	a	f	af	5
6	b	f	bf	6
7	c	f	cf	7
8	a	f	af	8
9
10
11	me	a
12	you	f
13		index		5
14		sumproduct		13
15		vlookup		5
Sheet4

This message was edited by on 2002-10-30 00:33

 

[Aladin Akyurek]

On 2002-10-30 00:23, IML wrote:

On 2002-10-30 00:05, Aladin Akyurek wrote:

On 2002-10-29 23:50, IML wrote:
what about a good, old fashion sumproduct?

[...]

Ian,

That's dangerous. change A8 to "a" and try Me=a and You=f.

Aladin

Good point, but what is the right answer here? 5, 8 or 13. I was (rightly or wrongly) assuming only unique combinations.

Playing with Colo's toy, I added your vlookup option (I will try not to get carried away with this)
[...]

If it's a retrieval problem, 5 and/or 8 are the right answers. I think you took it also as a retrieval problem: The trouble is that unique records are seldom guaranteed in such data, hence my note.

 

[Yogi Anand]

On 2002-10-29 23:37, Aladin Akyurek wrote:

On 2002-10-29 23:26, Yogi Anand wrote:
Hi gopa:

Or the following non-array formula:

=VLOOKUP(B10,{"a",1;"b",2;"c",3;"d",4},2,0)+MATCH(B11,B1:B8,0)-1
[...]

Sort the data on column A and try...

Me b
You f

Hi Aladin:
If the data is sorted, my formulation will fail. In my formulation, I had taken advantage of the way the data was laid out.

Regards!
Yogi Anand

 

[Yogi Anand]

One can use the DGET function ... see the following simulation:

y021030h1.xls
	A	B	C	D
1	MyCol	YourCol
2	c	f
3
4	MyCol	YourCol	Baby
5	a	e	1
6	b	e	2
7	c	e	3
8	d	e	4
9	a	f	5
10	b	f	6
11	c	f	7
12	d	f	8
13
14	My	c
15	Your	f
16	Baby	7
Sheet2

</SPAN>

Regards!
Yogi Anand

 

[Aladin Akyurek]

On 2002-10-30 10:34, Yogi Anand wrote:
One can use the DGET function ... see the following simulation:[...]

I don't know why you do this... Paddy has already quoted links that also show DGET as an approach to the multikey retrieval problems.

 

[Yogi Anand]

Sorry, I had not looked up the links that Paddy had pointed to, and that the use of DGET was already covered.

Regards!
Yogi Anand

 

[gopa]

Aladin

Thanks for the warning, but I'm only using it in 2 cells, so no problem.

Yogi

Thanks for your suggestion, but in real life my search criteria are in 2 separate cells on a different sheet from the databas, so Dget no help.

Apart from several solutions, the nicest thing about this is the amount of interest!

Hugh