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]

What version of Excel are you using?

 

[andycreber]

What version of Excel are you using?

Using MS Office Pro Plus 2016 cheers

 

[KRice]

When you say "match" to various columns on the zb sheet, do you mean that any matching values will be found on the same row...is that your definition of matching. Or do you mean that the searched for values will be found anywhere in the specified columns, perhaps a match is found in D4 (for the H3 value) and in H6 (for the G3 value)...does that constitute a match? I suspect you would want the match to occur on the same row, for example, perhaps the H3 and G3 values would be found on the zb! sheet in D4 and H4. And do you expect only one matching condition? MATCH will return the first that it finds.

 

[KRice]

Using this as a sample data table:

MrExcel_20220503.xlsx
	C	D	E	F	G	H
1	2	1				0
2	4	3				5
3	6	5				10
4	8	7				15
5	10	9				20
6	12	11				25
7	14	13				30
8	16	15				35
9	18	17				40
10	20	19				45
zb

I think the logic you described is shown in the K3 formula. In your description, you mention to do the same calculation regardless of whether H3>I3 or I3>H3. This is equivalent to saying that the two are not equal. The match of G3 to the zb!H column is required. The match to the zb!D column is also required, but the match may be made to either H3 or I3. Any matches are assumed to be required to occur on the same row, then the value in zb!C is returned.

MrExcel_20220503.xlsx
	F	G	H	I	J	K
1	H3>I3	same
2	I3>H3	match to zb!H	match to zb!D	match to zb!D		return zb!C
3		15	5	7		8
Sheet8

Cell Formulas
Range	Formula
K3	K3	=IF(I3<>H3,INDEX(zb!C1:C10,MATCH(1,(G3=zb!H1:H10)*(((H3=zb!D1:D10)+(I3=zb!D1:D10))>0),0)),"")

 

[andycreber]

Thanks for your assistance, I man that the searched for values will be found anywhere in the specified columns.

cells, G3 and I3 will all be on the same row but will be looking for data anywhere in columns

H3 is one value and I3 is another value, only one of those values should be found on the zb sheet in column D using criteria of cell G3 against column H in the zb tab

 

[Fluff]

Using MS Office Pro Plus 2016 cheers

I suggest that you update your Account details (or click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)

 

[KRice]

In your post #1, your starting formula contains components that require a match for G3, H3, and I3 on the same row of zb!H:H, zb!D:D, and zb!D:D, respectively (where you have (G3=zb!H:H) * (H3=zb!D:D) * (I3=zb!D:D) ). This is not possible, as H3 and I3 cannot both be found on the same row in the same column on the same sheet, unless H3 is always equal to I3. But your post #1 mentions "either column I or H" AND "and matches data in column G", so I'm assuming you are looking for the rows in column D of sheet zb! where you can find H3 or I3 (either one or even both can be found in zb!D:D)...that's what this part of the formula I posted does: (((H3=zb!D1:D10)+(I3=zb!D1:D10))>0). Then on that same row (or rows), you want to find a match between G3 and column H...that's what this part of the formula that I posted does: (G3=zb!H1:H10). Under those same-row matching conditions, the value in zb!C:C is returned, provided H3 is not equal to I3. This understanding may not be correct, as there are differences between the formula you posted and the description you offered...and the description is somewhat confusing.

Did you have a look at my post #5? Does it produce the expected result...or what is not correct with it? I've shortened the ranges for the example, but in practice you will want to expand them. I would suggest not using the entire column, but rather some range that is sufficiently large to cover your entire list.

 

[KRice]

Is the reason for making a distinction between H3>I3 or I3>H3 in your post #1 because you want to use only the maximum value between H3 and I3, and then whichever is the largest, you want to search for that value in zb!D:D? I'm just guessing...

 

[andycreber]

In your post #1, your starting formula contains components that require a match for G3, H3, and I3 on the same row of zb!H:H, zb!D:D, and zb!D:D, respectively (where you have (G3=zb!H:H) * (H3=zb!D:D) * (I3=zb!D:D) ). This is not possible, as H3 and I3 cannot both be found on the same row in the same column on the same sheet, unless H3 is always equal to I3. But your post #1 mentions "either column I or H" AND "and matches data in column G", so I'm assuming you are looking for the rows in column D of sheet zb! where you can find H3 or I3 (either one or even both can be found in zb!D:D)...that's what this part of the formula I posted does: (((H3=zb!D1:D10)+(I3=zb!D1:D10))>0). Then on that same row (or rows), you want to find a match between G3 and column H...that's what this part of the formula that I posted does: (G3=zb!H1:H10). Under those same-row matching conditions, the value in zb!C:C is returned, provided H3 is not equal to I3. This understanding may not be correct, as there are differences between the formula you posted and the description you offered...and the description is somewhat confusing.

Did you have a look at my post #5? Does it produce the expected result...or what is not correct with it? I've shortened the ranges for the example, but in practice you will want to expand them. I would suggest not using the entire column, but rather some range that is sufficiently large to cover your entire list.

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.