# index/match, based on more than one criteria

M

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

=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

Regards,
Marieke

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

=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

Regards,
Marieke

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.)