combination INDEX//MATCH and IF-functions

M

mariekek5

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

=INDEX(A1:C9;MATCH("L-1234";B1:B9;0);MATCH("ID number";A1:C1;0))

In the following case each product has an unique ID-code. Also each prodct
has a L-number, based on the supplier. I use the INDEX/MACTH combination to
lookup information. In this case I have the L number, and want to find the
correct ID number per supplier. So I want the ID number from L-1234 IF (in
case) the supplier is Green.

How can I combine the above mentioned INDEX/MATCH with an IF-function?
 
M

mariekek5

Thank you Dave. I tried your formula to practice, but unfortunately it doesnt
work...I am doing something wrong...

In my example...I want to search for a certain L-number (for instance the
"L-1234"), and then return the ID-number from a specific supplier.

So if I want to return the ID number from "L-1234" supplied by Holmer....I
would get number: 1237.

Can you help me with this specific case?
 
F

Fred Smith

When you say you "tried your formula to practice", what formula did you use?
It's very difficult to point you in the right direction without knowing
this. Try it this way:

=index(a1:a9,match(1,("L-1234"=b1:b9)*("Homer"=c1:c9),0))

Remember to use Ctrl-Shift-Enter to enter it.

Regards,
Fred
 
M

mariekek5

Sorry Fred, you are right. I did not make myself very clear.

Based on your advised I know used the following formula:

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

For some reason I now get the value "8769", which is from cell C9.

I dont understand why...
 
R

RagDyeR

That last asterisk should be a semi-colon:

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

--

HTH,

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

Sorry Fred, you are right. I did not make myself very clear.

Based on your advised I know used the following formula:

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

For some reason I now get the value "8769", which is from cell C9.

I dont understand why...
 
M

mariekek5

Thank you for your reply.
I will stay with the same thread from now on, thanks for the advise.

I used your formula below, with semi-colon instead of asterisk..but still it
does not work. I now get #value

RagDyeR said:
That last asterisk should be a semi-colon:

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

--

HTH,

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

Sorry Fred, you are right. I did not make myself very clear.

Based on your advised I know used the following formula:

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

For some reason I now get the value "8769", which is from cell C9.

I dont understand why...
 
F

Fred Smith

That's the same formula I used, and it returned 1237 for me. Are you sure
you entered it as an array formula (ctrl-shift-enter)?

Regards,
Fred
 
M

mariekek5

It works indeed. Thanks to you all!




Fred Smith said:
That's the same formula I used, and it returned 1237 for me. Are you sure
you entered it as an array formula (ctrl-shift-enter)?

Regards,
Fred
 
F

Fred Smith

In order to save yourself a lot of time in the future, when you're asking
questions on this board, the safest thing to assume is that the answer you
receive is correct. If it's not working, it's probably something you did.
This will allow you do implement solutions within an hour of posting a
question, as opposed to the two days that it actually took.

Regards,
Fred.
 
M

mariekek5

Dear Fred,

Thanks for your advise, you are totally right.

I have an additional question on the formula. Hope you can help me.

The fomula was:

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

Is it possible to put an extra name in with 'OR'. So that Excel will look
for a number beloning to Homer OR Janssen OR Van Gogh in c1:c9?

In my document, there never will be a number belonging to Homer and/or
Janssen and/or Van Gogh. But these three suppliers do have other
concurrents... As its quite a large document I am working with, it would be
nice to copy the fomula to the whole column.

Thanks a lot in advance.

Marieke
 
M

mariekek5

In addition. Is it also possible to ask for more 'AND's'?

Saying: I am looking for the x-code matching L-1234, if in column C it says
OR Homer, OR Van Gogh, OR Janssen, AND in colum E it says plastic.
 
F

Fred Smith

The ANDs are easy. You can add as many as you want, as in:
=INDEX(A1:A9;MATCH(1;("L-1234"=B1:B9)*("Homer"=C1:C9)*("Plastic"=E1:E9");0))

The ORs are more problematic. What cell do you want returned if, say, *both*
Homer and Van Gogh are in the range?

Regards,
Fred.
 
M

mariekek5

Great, thank you!

About the 'OR', in my document it will not happen that *both* Homer and Van
Gogh are in the range, because they are different names for the same
supplier.

Is it then possible to use the OR?
 

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

Top