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...

Atlantic Coast Zone	F2	F3	F4	F5	F6	F7	F8
Branch			Customer Name	Consignment Model	Q1		Q2
					Q1 Start	Q1 End	Q2 Start
					9/3/2017	12/2/2017	12/3/2017
					Date Completed	Status	Date Completed
AUG	58	Omitted	Omitted	Usage		Out of Compliance
GRV	178	Omitted	Omitted	Usage	10/26/17	Completed
AUG	188	Omitted	Omitted	Usage		Out of Compliance
CHS	324	Omitted	Omitted	Usage	10/4/17	Completed
CHS	330	Omitted	Omitted	Usage	10/26/17	Completed
AUG	375	Omitted	Omitted	Usage		Out of Compliance
Branch			Customer Name	Consignment Model	Q1 - Q2
					Q1 Start		Q2 End
					9/3/2017		3/3/2018
					Date Completed		Status
CHS	138	Omitted	Omitted	Replenishment			Count Required
GRV	218	Omitted	Omitted	Replenishment			Count Required
CHS	340	Omitted	Omitted	Replenishment			Count Required
AUG	422	Omitted	Omitted	Replenishment			Count Required
GRV	424	Omitted	Omitted	Replenishment			Count Required

<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>
</tbody>

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

ISS Cust #	AS 400#	Customer Name	Account Type	Consignment Model	Branch	Zone	Inventory Value	Q1	Q2	Q3	Q4
47	Omitted	Omitted	CAP	Replenishment	GPD	Great Lakes	Omitted
48	Omitted	Omitted	CAP	Replenishment	GPD	Great Lakes	Omitted
49	Omitted	Omitted	CAP	Replenishment	GPD	Great Lakes	Omitted
50	Omitted	Omitted	CAP	Usage	GPD	Great Lakes	Omitted
58	Omitted	Omitted	Shelf/SAP	Usage	AUG	Atlantic Coast	Omitted
59	Omitted	Omitted	Shelf/SAP	Usage	LTR	Heartland	Omitted
60	Omitted	Omitted	CAP	Replenishment	JIS	Southeast Central	Omitted
73	Omitted	Omitted	CAP	Usage	GPD	Great Lakes	Omitted

<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>
</tbody>

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!