Knowing me, knowing you, choose baby

gopa

New Member
Joined
Oct 23, 2002
Messages
32
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.
 

Excel Facts

Last used cell?
Press Ctrl+End to move to what Excel thinks is the last used cell.
Hi:

Please clarify right baby's number....and out come you expect to see....
pll
 
Upvote 0
On 2002-10-29 21:45, PaddyD wrote:
with the posted sample in a1:c8,

=INDEX(C1:C8,MATCH("a",IF(B1:B8="e",A1:A8),0))

this needs to be array entered (using control + shift + enter, not just enter).

see

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

for more info.

paddy

You can also dispense with the IF...

=INDEX(C1:C8,MATCH(1,(A1:A8="a")*(B1:B8="e"),0))

which is also array-entered.
 
Upvote 0
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
y021029U1.xls
ABCD
1ae1
2be2
3ce3
4de4
5af5
6bf6
7cf7
8df8
9
10Mea
11Youe
12Baby1
Sheet2



Regards!
Yogi Anand
 
Upvote 0
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
 
Upvote 0
what about a good, old fashion sumproduct?
Book2
ABCD
1ae1
2be2
3ce3
4de4
5af5
6bf6
7cf7
8df8
9
10
11mea
12youe
13index1
14sumproduct1
Sheet4

This message was edited by IML on 2002-10-29 23:53
 
Upvote 0
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
 
Upvote 0

Forum statistics

Threads
1,213,536
Messages
6,114,215
Members
448,554
Latest member
Gleisner2

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top