If statement to evaluate the next record...

**nessabell3** · 02-18-2013, 03:40 PM

I am trying to set up a template to make my month end processing easier. I want to automatically pull data from a Details worksheet that has an "I" in column E to a worksheet called Insurance, in the same workbook. I need to return all values into the corresponding colums A:P. The problem is, there are H's and P's intermixed with the I's. I'm not sure of the best way to approach this issue. Vlookup seems like it might work...but if E2<>I, I have no idea how to get it to evaluate the next record...

Thanks!
G

**Jacc** · 02-18-2013, 08:17 PM

With the help of an index number column it can be done. Possibly it can be done without the index number column as well but the hour is waay to late for that now.

I can try again tomorrow.

**Jacc** · 02-19-2013, 02:38 AM

It was indeed possible.
Formula now looks like:

Formula:

Please Login or Register  to view this content.

It's an array formula by the way.

**benishiryo** · 02-19-2013, 03:01 AM

hi nessabell3, welcome to the forum. it's always good if you can upload a sample Excel file. here's an alternative in cell A2 of "Insurance" sheet:

Formula:

Please Login or Register  to view this content.

change all row 10 to something bigger to fit in your data. you can copy the formula down or across as much as you like after that. being an array formula, you have to paste the formula inside the formula bar & press CTRL + SHIFT + ENTER. When done correctly, you should see curly brackets surrounding it

**amitkr3855** · 02-19-2013, 03:12 AM

In A4
note this is an array formula need to Cntrl + Enter

=IFERROR(INDEX(Sheet2!$D$2:$D$26,SMALL(IF(Sheet2!$E$2:$E$26="I",ROW(INDIRECT("1:"&ROWS(Sheet2!$D$2:$D$26))),""),ROWS(Sheet1!$D$3:D3))),"")

In B4 and drag it to C4
=SUMIFS(Sheet2!F:F,Sheet2!$D:$D,Sheet1!$A4)

R's
Amit Kumar