combination INDEX//MATCH and IF-functions

  • Thread starter Thread starter mariekek5
  • Start date Start date
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?
 
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?
 
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
 
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...
 
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...
 
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...
Click to expand...
 
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
 
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
Click to expand...
 
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.
 
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
 
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.
 
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.
 
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?
 
Back
Top