Nested IF Statement with INDEX and MATCH

**utahguy9384** · 05-22-2014, 03:22 PM

HI I am having a problem figuring something out, if someone could help I would appreciate it.

I have three worksheets the first one called Econ the second one called CheckLogs and the third is a customer sheet. I am needing to match up a cell from the Econ sheet with a cell from the Checklogs sheet and then it matches a cell from the customer sheet with a cell from the checklogs to determine what customer sheet. My problem is how do I loop through the customer code cell until it finds the correct cell.

=IF(B1=CheckLogs!B2,INDEX(CheckLogs!$F$2:$F$4493,MATCH(Econ!G2,CheckLogs!$C$2:$C$4493,0),MATCH($B$1,CheckLogs!$B2:B4493,0)), "")

so in the formula "B1" is located on the customer sheet and is the customer code field, "CheckLogs!B2" is on the CheckLogs sheet and is the customer code field. I'm just not sure how you loop if the customer code in B1 doesn't equal the customer code in CheckLogs!B2 and run through the formula again.

Sorry if this doesn't make sense.

**Pete_UK** · 05-22-2014, 03:45 PM

It might help if you attach a sample workbook.

Pete

**Sirmarte** · 05-22-2014, 03:55 PM

Hi,

I have one worksheet. I am needing to match up column G with value on $E$3 and give consecutive numbers on column F depending if the matched column G with value on $E$3 is on same date of column L; I have plenty blankcells and other values on column G.
I trying catch up
Thanks 4 your help

**utahguy9384** · 05-22-2014, 04:09 PM

Sorry about that here is a sample Workbook

**newdoverman** · 05-22-2014, 05:23 PM

As I understand the problem, this should solve it.

**utahguy9384** · 05-22-2014, 06:12 PM

@newdoverman Thank you so much worked perfectly. I know I didn't explain it very well and you totally nailed it for me. Thank you again

**newdoverman** · 05-22-2014, 06:21 PM

You're welcome. I'm glad that it worked out for you.

**utahguy9384** · 05-23-2014, 12:19 PM

@newdoverman if you don't mind could you give me a quick explaination of the formula you used to make this work. I just want to make sure I understand what it's doing.

=IFERROR(INDEX(Econ!C$2:C$4500,SMALL(IF(Econ!$I$2:$I$4500=$B$1,ROW(Econ!$I$2:$I$4500)-MIN(ROW(Econ!$I$2:$I$4500))+1),ROWS($1:1))),"")

**newdoverman** · 05-23-2014, 02:10 PM

Starting in the middle of the formula, this is roughly how this goes:

The IF statement starts off with Econ!$I$2:$I$100=$B$1. This searches Econ!$I2:$I100 for cells that equal B1 which is 1USC. This returns a list in order giving TRUE for every cell that matches and FALSE for every cell that doesn't match.

ROW(Econ!$I$2:$I$100)-MIN(ROW(Econ!$I$2:$I$100))+1 is the value for TRUE of the IF statement. Gives a listing of all the rows in the range $I$2:$I$100. This is a listing of 1 to 99. There isn't a value for FALSE given.
The IF statement is surrounded by a SMALL statement SMALL(IF(Econ!$I$2:$I$100=$B$1,ROW(Econ!$I$2:$I$100)-MIN(ROW(Econ!$I$2:$I$100))+1),ROWS($1:1)) The SMALL statement has two parts, the ARRAY and the K.

The array part is IF(Econ!$I$2:$I$100=$B$1,ROW(Econ!$I$2:$I$100)-MIN(ROW(Econ!$I$2:$I$100))+1) This returns a FALSE for cells that don't have 1USC and row numbers for cells that do have 1USC which is the value in B1.

The K part of the SMALL function arguments is ROWS($1:1) is a counter that increments by 1 every time the array part of the SMALL function returns a number. This allows the formula to return the row number where the values sought are in the order in which they are found.

INDEX surrounds all of the above and identifies from where the values sought are to be found. In this case the values are in Econ!A$2:A$100 using the rows determined from the above SMALL(IF.... part of the formula.

IFERROR surrounds the whole formula above and if the result is an error it returns the value between the "" at the end of the formula.

I hope that this is understandable.

**newdoverman** · 05-23-2014, 02:15 PM

Starting in the middle of the formula, this is roughly how this goes:

The IF statement starts off with Econ!$I$2:$I$100=$B$1. This searches Econ!$I2:$I100 for cells that equal B1 which is 1USC. This returns a list in order giving TRUE for every cell that matches and FALSE for every cell that doesn't match.

ROW(Econ!$I$2:$I$100)-MIN(ROW(Econ!$I$2:$I$100))+1 is the value for TRUE of the IF statement. Gives a listing of all the rows in the range $I$2:$I$100. This is a listing of 1 to 99. There isn't a value for FALSE given.
The IF statement is surrounded by a SMALL statement SMALL(IF(Econ!$I$2:$I$100=$B$1,ROW(Econ!$I$2:$I$100)-MIN(ROW(Econ!$I$2:$I$100))+1),ROWS($1:1)) The SMALL statement has two parts, the ARRAY and the K.

The array part is IF(Econ!$I$2:$I$100=$B$1,ROW(Econ!$I$2:$I$100)-MIN(ROW(Econ!$I$2:$I$100))+1) This returns a FALSE for cells that don't have 1USC and row numbers for cells that do have 1USC which is the value in B1.

The K part of the SMALL function arguments is ROWS($1:1) is a counter that increments by 1 every time the array part of the SMALL function returns a number. This allows the formula to return the row number where the values sought are in the order in which they are found.

INDEX surrounds all of the above and identifies from where the values sought are to be found. In this case the values are in Econ!A$2:A$100 using the rows determined from the above SMALL(IF.... part of the formula.

IFERROR surrounds the whole formula above and if the result is an error it returns the value between the "" at the end of the formula.

The above is ARRAY ENTERED (Ctrl + Shift + Enter) because it is calculating ranges of cells with other ranges of cells and there is no function overriding the requirement for an array formula. SUMPRODUCT is an example of a function that does array type calculations without requiring ARRAY entry. That condition isn't present in this formula.
I hope that this is understandable.

**utahguy9384** · 05-23-2014, 03:31 PM

Yes makes sense and thank you for your time to explain. Thank you again

**newdoverman** · 05-23-2014, 03:41 PM

You're welcome.

Thank you for the feedback.

Nested IF Statement with INDEX and MATCH

LinkBack

Thread Tools

Rate This Thread

Display

Nested IF Statement with INDEX and MATCH

Re: Nested IF Statement with INDEX and MATCH

Re: Nested IF Statement with INDEX and MATCH

Re: Nested IF Statement with INDEX and MATCH

Re: Nested IF Statement with INDEX and MATCH

Re: Nested IF Statement with INDEX and MATCH

Re: Nested IF Statement with INDEX and MATCH

Re: Nested IF Statement with INDEX and MATCH

Re: Nested IF Statement with INDEX and MATCH

Re: Nested IF Statement with INDEX and MATCH

Re: Nested IF Statement with INDEX and MATCH

Re: Nested IF Statement with INDEX and MATCH

Thread Information

Users Browsing this Thread

Similar Threads

[SOLVED] 3 Nested IF INDEX MATCH

[SOLVED] Combining Nested IF statement with Index/Match??

nested if statement with index and match within

Nested Match(index()) vs. Match() array formula.

Nested IF (index and match)

Bookmarks

Bookmarks

Posting Permissions