What do I use? Index? Match? If? Combination of all three?

[justincaza]

Good afternoon,
I have a workbook. Within that workbook I have two worksheets, one is called customer list and the second is called raw data. The raw data worksheet is set up like below...

[TABLE="class: grid, width: 946"]
<colgroup><col width="129" style="width:97pt"> <col width="31" style="width:23pt"> <col width="63" style="width:47pt"> <col width="254" style="width:191pt"> <col width="130" style="width:98pt"> <col width="108" style="width:81pt"> <col width="123" style="width:92pt"> <col width="108" style="width:81pt"> </colgroup><tbody>[TR]
[TD="width: 129"]Atlantic Coast Zone[/TD]
[TD="width: 31"]F2[/TD]
[TD="width: 63"]F3[/TD]
[TD="width: 254"]F4[/TD]
[TD="width: 130"]F5[/TD]
[TD="width: 108"]F6[/TD]
[TD="width: 123"]F7[/TD]
[TD="width: 108"]F8[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Branch[/TD]
[TD][/TD]
[TD][/TD]
[TD]Customer Name[/TD]
[TD]Consignment Model[/TD]
[TD]Q1[/TD]
[TD][/TD]
[TD]Q2[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]Q1 Start[/TD]
[TD]Q1 End[/TD]
[TD]Q2 Start[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]9/3/2017[/TD]
[TD]12/2/2017[/TD]
[TD]12/3/2017[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]Date Completed[/TD]
[TD]Status[/TD]
[TD]Date Completed[/TD]
[/TR]
[TR]
[TD]AUG[/TD]
[TD="align: right"]58[/TD]
[TD]Omitted[/TD]
[TD]Omitted[/TD]
[TD]Usage[/TD]
[TD][/TD]
[TD]Out of Compliance[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]GRV[/TD]
[TD="align: right"]178[/TD]
[TD]Omitted[/TD]
[TD]Omitted[/TD]
[TD]Usage[/TD]
[TD]10/26/17[/TD]
[TD]Completed[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]AUG[/TD]
[TD="align: right"]188[/TD]
[TD]Omitted[/TD]
[TD]Omitted[/TD]
[TD]Usage[/TD]
[TD][/TD]
[TD]Out of Compliance[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]CHS[/TD]
[TD="align: right"]324[/TD]
[TD]Omitted[/TD]
[TD]Omitted[/TD]
[TD]Usage[/TD]
[TD]10/4/17[/TD]
[TD]Completed[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]CHS[/TD]
[TD="align: right"]330[/TD]
[TD]Omitted[/TD]
[TD]Omitted[/TD]
[TD]Usage[/TD]
[TD]10/26/17[/TD]
[TD]Completed[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]AUG[/TD]
[TD="align: right"]375[/TD]
[TD]Omitted[/TD]
[TD]Omitted[/TD]
[TD]Usage[/TD]
[TD][/TD]
[TD]Out of Compliance[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Branch[/TD]
[TD][/TD]
[TD][/TD]
[TD]Customer Name[/TD]
[TD]Consignment Model[/TD]
[TD]Q1 - Q2[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]Q1 Start[/TD]
[TD][/TD]
[TD]Q2 End[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]9/3/2017[/TD]
[TD][/TD]
[TD]3/3/2018[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]Date Completed[/TD]
[TD][/TD]
[TD]Status[/TD]
[/TR]
[TR]
[TD]CHS[/TD]
[TD="align: right"]138[/TD]
[TD]Omitted[/TD]
[TD]Omitted[/TD]
[TD]Replenishment[/TD]
[TD][/TD]
[TD][/TD]
[TD]Count Required[/TD]
[/TR]
[TR]
[TD]GRV[/TD]
[TD="align: right"]218[/TD]
[TD]Omitted[/TD]
[TD]Omitted[/TD]
[TD]Replenishment[/TD]
[TD][/TD]
[TD][/TD]
[TD]Count Required[/TD]
[/TR]
[TR]
[TD]CHS[/TD]
[TD="align: right"]340[/TD]
[TD]Omitted[/TD]
[TD]Omitted[/TD]
[TD]Replenishment[/TD]
[TD][/TD]
[TD][/TD]
[TD]Count Required[/TD]
[/TR]
[TR]
[TD]AUG[/TD]
[TD="align: right"]422[/TD]
[TD]Omitted[/TD]
[TD]Omitted[/TD]
[TD]Replenishment[/TD]
[TD][/TD]
[TD][/TD]
[TD]Count Required[/TD]
[/TR]
[TR]
[TD]GRV[/TD]
[TD="align: right"]424[/TD]
[TD]Omitted[/TD]
[TD]Omitted[/TD]
[TD]Replenishment[/TD]
[TD][/TD]
[TD][/TD]
[TD]Count Required[/TD]
[/TR]
</tbody>[/TABLE]

The customer list work sheet is setup like below...

[TABLE="class: grid, width: 1039"]
<colgroup><col width="74" style="width:56pt"> <col width="63" style="width:47pt"> <col width="302" style="width:227pt"> <col width="93" style="width:70pt"> <col width="130" style="width:98pt"> <col width="50" style="width:38pt"> <col width="115" style="width:86pt"> <col width="104" style="width:78pt"> <col width="27" style="width:20pt" span="4"> </colgroup><tbody>[TR]
[TD="width: 74"]ISS Cust #[/TD]
[TD="width: 63"]AS 400#[/TD]
[TD="width: 302"]Customer Name[/TD]
[TD="width: 93"]Account Type[/TD]
[TD="width: 130"]Consignment Model[/TD]
[TD="width: 50"]Branch[/TD]
[TD="width: 115"]Zone[/TD]
[TD="width: 104"]Inventory Value[/TD]
[TD="width: 27"]Q1[/TD]
[TD="width: 27"]Q2[/TD]
[TD="width: 27"]Q3[/TD]
[TD="width: 27"]Q4[/TD]
[/TR]
[TR]
[TD="align: right"]47[/TD]
[TD]Omitted[/TD]
[TD]Omitted[/TD]
[TD]CAP[/TD]
[TD]Replenishment[/TD]
[TD]GPD[/TD]
[TD]Great Lakes[/TD]
[TD]Omitted[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]48[/TD]
[TD]Omitted[/TD]
[TD]Omitted[/TD]
[TD]CAP[/TD]
[TD]Replenishment[/TD]
[TD]GPD[/TD]
[TD]Great Lakes[/TD]
[TD]Omitted[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]49[/TD]
[TD]Omitted[/TD]
[TD]Omitted[/TD]
[TD]CAP[/TD]
[TD]Replenishment[/TD]
[TD]GPD[/TD]
[TD]Great Lakes[/TD]
[TD]Omitted[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]50[/TD]
[TD]Omitted[/TD]
[TD]Omitted[/TD]
[TD]CAP[/TD]
[TD]Usage[/TD]
[TD]GPD[/TD]
[TD]Great Lakes[/TD]
[TD]Omitted[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]58[/TD]
[TD]Omitted[/TD]
[TD]Omitted[/TD]
[TD]Shelf/SAP[/TD]
[TD]Usage[/TD]
[TD]AUG[/TD]
[TD]Atlantic Coast[/TD]
[TD]Omitted[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]59[/TD]
[TD]Omitted[/TD]
[TD]Omitted[/TD]
[TD]Shelf/SAP[/TD]
[TD]Usage[/TD]
[TD]LTR[/TD]
[TD]Heartland[/TD]
[TD]Omitted[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]60[/TD]
[TD]Omitted[/TD]
[TD]Omitted[/TD]
[TD]CAP[/TD]
[TD]Replenishment[/TD]
[TD]JIS[/TD]
[TD]Southeast Central[/TD]
[TD]Omitted[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]73[/TD]
[TD]Omitted[/TD]
[TD]Omitted[/TD]
[TD]CAP[/TD]
[TD]Usage[/TD]
[TD]GPD[/TD]
[TD]Great Lakes[/TD]
[TD]Omitted[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

This is what I'm trying to accomplish...In the Q1 column on the customer list, I need the value that is in column F7 OR F8 from the raw data worksheet where the ISS customer number in the customer list matches the customer number in the raw data in column F2. Both sheets are setup as tables. As you can see the value I'm looking for can either be in column F7 OR column F8. I've been researching for a couple days and I cannot figure this out. Can someone please help me figure out what this formula would be and understand it.

 

[macattackpro]

HI,

Does it matter if both "F7" and "F8" have data in them? Is there a preference on which one to return over the other? Try the below to prefer "F7" over "F8" (I'm using actual column letters in the formula too btw)

=IF(ISBLANK(INDEX(RAW!G:G,MATCH(A2,RAW!B:B,0))),INDEX(RAW!H:H,MATCH(A2,RAW!B:B,0)),INDEX(RAW!G:G,MATCH(A2,RAW!B:B,0)))

Of course you could return both through a concatenation:

=INDEX(RAW!G:G,MATCH(A2,RAW!B:B,0))&" / "&INDEX(RAW!H:H,MATCH(A2,RAW!B:B,0))

 

[justincaza]

HI,

Does it matter if both "F7" and "F8" have data in them? Is there a preference on which one to return over the other? Try the below to prefer "F7" over "F8" (I'm using actual column letters in the formula too btw)

=IF(ISBLANK(INDEX(RAW!G:G,MATCH(A2,RAW!B:B,0))),INDEX(RAW!H:H,MATCH(A2,RAW!B:B,0)),INDEX(RAW!G:G,MATCH(A2,RAW!B:B,0)))

Of course you could return both through a concatenation:

=INDEX(RAW!G:G,MATCH(A2,RAW!B:B,0))&" / "&INDEX(RAW!H:H,MATCH(A2,RAW!B:B,0))

The value I need to return can either be in column F7 OR in column F8. The determining factor would be based in column F2 and F5. If column F2 has a number AND column F5 has "usage" then I need what is in column F7 returned in the customer list sheet. If column F2 has a number AND column F5 has "replenishment" then I need what is in column F8 returned in the customer sheet. I'm hoping I can just copy down the formula.

 

[macattackpro]

Give this a try. It considers the "Usage"/"Replenishment" requirement. If there is something other than "Usage" or "Replenishment" in that column it will return a blank cell. It's also assuming that any customer number listed will be in the RAW Data sheet. If it is not then it will return an error.

=IF(E2="Usage",INDEX('raw data'!G:G,MATCH(A2,'raw data'!B:B,0)),IF(E2="Replenishment",INDEX('raw data'!H:H,MATCH(A2,'raw data'!B:B,0)),""))

 

[justincaza]

Works perfect, thank you! I've been trying to get this down for a couple of days now. I typed out the code to help me understand where I went wrong and found it, so thank you for helping me.

 

[macattackpro]

You're welcome! Enjoy!