index/match, based on more than one criteria

[mariekek5]

I already got some advise on this matter (thanks Fred and Dave), but still I
do not get what I want.

Based on a L-number, I want to find the correct ID-number belonging to that
product from a specific supplier. So if I want the ID number from L-1234
belonging to supplier “Homer†… I would like to get the value “1237â€.

Using the following formula I get “8769â€â€¦which is the value from cell
A9…instead of A4.

=INDEX(A1:A9;MATCH(1;("L-1234"=B1:B9)*("Homer"=C1:C9)*0))

ID number L-number Supplier
1245 L-1234 Green
3456 L-1234 Smith
1237 L-1234 Homer
3456 L-3256 McDonald
2423 L-2435 Homer
7765 L-2222 Green
2543 L-2222 Smith
8769 L-5678 MCDonald

I hope someone can help me out here

Thanks in advance.

Regards,
Marieke

 

[RagDyeR]

Check your original post for answers.

It's best to stay with one thread for the same issue!
--

Regards,

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

I already got some advise on this matter (thanks Fred and Dave), but still I
do not get what I want.

Based on a L-number, I want to find the correct ID-number belonging to that
product from a specific supplier. So if I want the ID number from L-1234
belonging to supplier "Homer" . I would like to get the value "1237".

Using the following formula I get "8769".which is the value from cell
A9.instead of A4.

=INDEX(A1:A9;MATCH(1;("L-1234"=B1:B9)*("Homer"=C1:C9)*0))

ID number L-number Supplier
1245 L-1234 Green
3456 L-1234 Smith
1237 L-1234 Homer
3456 L-3256 McDonald
2423 L-2435 Homer
7765 L-2222 Green
2543 L-2222 Smith
8769 L-5678 MCDonald

I hope someone can help me out here

Thanks in advance.

Regards,
Marieke

 

[DILipandey]

Paste below data to cover columns A to F.

ID number L-number Supplier L-number Supplier ID number
1245 L-1234 Green L-1234 Homer <see below>
3456 L-1234 Smith
1237 L-1234 Homer
3456 L-3256 McDonald
2423 L-2435 Homer
7765 L-2222 Green
2543 L-2222 Smith
8769 L-5678 MCDonald

replace <see below> with following formula:-
=DGET($A$1:$C$9,$A$1,$D$1:$E$2)
It will do the needful
--
Click on Yes, if it is useful.

Thanks & Best Regards,
Dilip Kumar Pandey
MBA, BCA, B.Com(Hons.)
(e-mail address removed)
(e-mail address removed)
New Delhi, India