Help with multiple index matches with multiple if statements

[andycreber]

Hi, I'm trying to write a index match formula with 2 if statements.

So far my formula is

=IF(I3>H3,INDEX(zb!C:C,MATCH(1,(I3=zb!D:D)*(G3=zb!H:H)*(H3=zb!D:D),0)),(IF(H3>I3,INDEX(zb!C:C,MATCH(1,(H3=zb!D:D)*(G3=zb!H:H)*(H3=zb!D:D),0)))))

I want to return the data if I3 > H3 then match the data on sheet zb! column C if the value matches in either column I or H and matches data in column G against the data in column H on tab zb or if H3>I3 then do the same match

Many thanks in advance

 

[KRice]

If G3 and H3 are equal and you expect the formula to return a value, then the lookup values in G3 and H3 need to be on the same row in the zb worksheet in both columns D and H...then the corresponding value in column C of the zb worksheet will be returned. Have you confirmed that a match should be found? Can you provide an example?

 

[andycreber]

Hi KRice, Your #5 post formula is working (thank you) when the values are different between between G3 & H3, but I have some instances where the values are the same, but the formula is not working when this is the case.

Hi, Column H is doing a sum if for any value with the same reference in column G as there can be multiple lines with the same reference in column G, this means the values between column H and I will be different, in the sense of column H will be a larger figure that column I. The reason for this is that sometimes there are multiple lines of data with the same reference in column G but different amounts and always I want to return the unique data from the zb tab in column C. I very much appreciate your help and sorry that it is confusing.

 

[KRice]

Realize that I have never seen your worksheets. You have described one worksheet that has values in cells G3, H3, and I3. You have described another worksheet called "zb" that apparently has values in columns C, D, and H. Now, in your last post, you mention "same reference in column G", and you also mention column H and I. I cannot understand which sheet you are referring to, which is why I asked for an example...or anything that would clarify what your sheets look like and what you are trying to accomplish.

I explained in an earlier post what my formula does, but if that is not reliably working to return expected results, then I will need a clearer description of the problem. For example, in post #10, you said the formula is working, but...

but I have some instances where the values are the same, but the formula is not working when this is the case

What values are the same? Values found in columns D and H on the zb worksheet, or values in G3, H3, and I3 on the main sheet?

Please feel free to install the XL2BB add-in (see the link in my signature block) so that you can share an example, or post a table or image explaining what you have and what you want to match.

 

[andycreber]

z1 tab

zb tab

z1 with formaulas shown

z1 formulas cont...

Sorry I cannot upload the spreadsheet, hope the screenshots help The values can be the same in column H & I on the z1 tab

thank you

 

[KRice]

In your first post, you indicated that a condition for the lookup to occur was that H3<I3 or I3<H3. In other words, H3 is not equal to I3. In your latest post, you have H3=I3, so the lookup will not occur because the first IF statement prevents that from happening. If this is indeed the issue and you want the lookup to occur even when H3=I3, then the logical check in the IF statement is not needed. As a side note, your previous post #10 mentions:

when the values are different between between G3 & H3

...which is referring to different columns and added to my confusion.

In your latest example, why would you expect an answer of "CAT" when CAT is not found in column C of the zb worksheet? If the match conditions are met, the formula will return a value only from column C of the zb worksheet. And if you do not care whether the columns H and I values on the z1 worksheet are equal, the answer returned would be TOM.

Book2
	A	B	C	D	E	F	G	H
1		Jrnl.Entry	Journal Entry	Amount			REF	REF9
2		DOG	REX	-1333.38			DOG	DOG
3		DOG	ROVER	-952.7			DOG	DOG
4		CAT	TOM	-2.86			CAT	CAT
zb

The color coding shows which values in the z1 worksheet are searched for in selected columns of the zb worksheet (yellow and blue), and the column in the zb worksheet from which the value is taken and returned back to the formula in the z1 worksheet (green).

Book2
	G	H	I	J
1	REF9	Negative for sum	Amount for lookup	ZB MATCH BY REF & AMOUNT
2	match to zb!H	match to zb!D	match to zb!D	return zb!C
3	DOG	-1333.38	-1083.38	REX
4	DOG	-1333.38	250	REX
5	CAT	-2.86	-2.86	TOM
z1

Cell Formulas
Range	Formula
J3:J5	J3	=IFERROR(INDEX(zb!$C$2:$C$10,MATCH(1,(G3=zb!$H$2:$H$10)*(((H3=zb!$D$2:$D$10)+(I3=zb!$D$2:$D$10))>0),0)),"no match")

 

[andycreber]

Hi,

Very sorry that I made the posts confusing, it's confusing for me, however I think your last formula re J3 is working, I would like to thank you very much for your help and patience with this query.
5 stars

have a great day

 

[KRice]

That's good to hear, and I'm happy to help. To clarify, the latest formula completely ignores the relationship between values in columns H and I on the z1 worksheet. If the match criteria are not met, the INDEX/MATCH formula would return an error, so the entire formula is wrapped with an IFERROR function to return the result of "no match" in those cases.