Hi,
I have been using excel help function but not getting my intended results; I always receive data in EXCEL as follows:
· Column A = RL (145
· Column B = ROCKGROUP (GKSAP)
· Column C = GRADEGROUP (WASTE)
· Column D = Volume (1582.236)

· Column A = RL (145
· Column B = ROCKGROUP (GKSAP)
· Column C = GRADEGROUP (MO)
· Column D = Volume (81582.236)
This data/table continues for all the data I need to analyze. Data in this formation takes much of my time to analyze it and not presentable. I have 12 Rock Groups (GKSAP; GKTR; GKFR; FWSAP; FWTR; FWFR; HWSAP; HWTR; HWFR; SOFTWAST; TRNSWAST; HARDWAST); 5 grade groups (WASTE; MO; LG; MG; HG) and 70 Levels (145.0 to 0.0) to analyze within 24hours and present.
I am much familiar with VLOOKUP/HLOOKUP. I am unable or don’t know how to use these functions with such an exercise. I am getting a 0 result or #N/A. my formula is as follows:


=VLOOKUP(A13;bench!$C$6:$F$1706;IF($C$1="GKSAP";2; IF($C$1="GKTR";2;IF($C$1="GKFR";2;IF($C$1="FWSAP"; 2;IF($C$1="FWTR";2;IF($C$1="FWFR";2;IF($C$1="HWSAP ";2;IF($C$1="HWTR";2;IF($C$1="HWFR";2;IF($C$1="SOF TWAST";2;IF($C$1="TRNSWAST";2;IF($C$1="HARDWAST";2 ;IF($C$2="WASTE";3;IF($C$2="mo";3;IF($C$2="mo";3;I F($C$2="lg";3;IF($C$2="mg";3;IF($C$2="hg";3;TRUE)) )))))))))))))))))
So please I need your help if there are any functions or is my formula not correct. I do arrange it in this formation (Table below) for better analysis and presentation. I want to arrange my table in this formation:
· Column A = RL (Cell A3 being my RL value)
· Column B = RL (Cell B1 being my ROCKGROUP; Cell B2 being my GRADEGROUP)
· Column C = RL (Cell C1 being my ROCKGROUP; Cell C2 being my GRADEGROUP)
· From Row 3 (B3) in reference to the RL the volumes should

That is the most complicated formula I have ever seen. Could you attach a sample of your worksheet so we can try to help you with a more conventional solution.

RL ROCKGROUP GRADEGROUP Volume BCM 145.00 GKSAP WASTE - 145.00 GKSAP MO - 145.00 GKSAP LG - 145.00 GKSAP MG - 145.00 GKSAP HG - 145.00 145.00 GKTR WASTE - 145.00 GKTR MO - 145.00 GKTR LG - 145.00 GKTR MG - 145.00 GKTR HG - 145.00 145.00 GKFR WASTE - 145.00 GKFR MO - 145.00 GKFR LG - 145.00 GKFR MG - 145.00 GKFR HG - 145.00 145.00 FWSAP WASTE - 145.00 FWSAP MO - 145.00 FWSAP LG - 145.00 FWSAP MG - 145.00 FWSAP HG - 145.00 145.00 FWTR WASTE - 145.00 FWTR MO - 145.00 FWTR LG - 145.00 FWTR MG - 145.00 FWTR HG - 145.00 145.00 FWFR WASTE - 145.00 FWFR MO - 145.00 FWFR LG - 145.00 FWFR MG - 145.00 FWFR HG - 145.00 145.00 HWSAP WASTE - 145.00 HWSAP MO - 145.00 HWSAP LG - 145.00 HWSAP MG - 145.00 HWSAP HG - 145.00 145.00 HWTR WASTE - 145.00 HWTR MO - 145.00 HWTR LG - 145.00 HWTR MG - 145.00 HWTR HG - 145.00 145.00 HWFR WASTE - 145.00 HWFR MO - 145.00 HWFR LG - 145.00 HWFR MG - 145.00 HWFR HG - 145.00 145.00 SOFTWAST WASTE - 145.00 145.00 TRNSWAST WASTE - 145.00 145.00 HARDWAST WASTE 9.01 145.00 142.50 GKSAP WASTE - 142.50 GKSAP MO 0.42 142.50 GKSAP LG - 142.50 GKSAP MG - 142.50 GKSAP HG - 142.50 142.50 GKTR WASTE - 142.50 GKTR MO - 142.50 GKTR LG - 142.50 GKTR MG - 142.50 GKTR HG - 142.50 142.50 GKFR WASTE - 142.50 GKFR MO - 142.50 GKFR LG - 142.50 GKFR MG - 142.50 GKFR HG - 142.50 142.50 FWSAP WASTE - 142.50 FWSAP MO - 142.50 FWSAP LG - 142.50 FWSAP MG - 142.50 FWSAP HG - 142.50 142.50 FWTR WASTE - 142.50 FWTR MO - 142.50 FWTR LG - 142.50 FWTR MG - 142.50 FWTR HG - 142.50 142.50 FWFR WASTE - 142.50 FWFR MO - 142.50 FWFR LG - 142.50 FWFR MG - 142.50 FWFR HG - 142.50 142.50 HWSAP WASTE - 142.50 HWSAP MO - 142.50 HWSAP LG - 142.50 HWSAP MG - 142.50 HWSAP HG - 142.50 142.50 HWTR WASTE - 142.50 HWTR MO - 142.50 HWTR LG - 142.50 HWTR MG - 142.50 HWTR HG - 142.50 142.50 HWFR WASTE - 142.50 HWFR MO - 142.50 HWFR LG - 142.50 HWFR MG - 142.50 HWFR HG - 142.50 142.50 SOFTWAST WASTE 288.95 142.50 142.50 TRNSWAST WASTE - 142.50 142.50 HARDWAST WASTE 5,975.62 142.50 140.00 GKSAP WASTE - 140.00 GKSAP MO - 140.00 GKSAP LG - 140.00 GKSAP MG - 140.00 GKSAP HG - 140.00 140.00 GKTR WASTE - 140.00 GKTR MO - 140.00 GKTR LG - 140.00 GKTR MG - 140.00 GKTR HG - 140.00 140.00 GKFR WASTE - 140.00 GKFR MO - 140.00 GKFR LG - 140.00 GKFR MG - 140.00 GKFR HG - 140.00 140.00 FWSAP WASTE - 140.00 FWSAP MO - 140.00 FWSAP LG - 140.00 FWSAP MG - 140.00 FWSAP HG - 140.00 140.00 FWTR WASTE - 140.00 FWTR MO - 140.00 FWTR LG - 140.00 FWTR MG - 140.00 FWTR HG - 140.00 140.00 FWFR WASTE - 140.00 FWFR MO - 140.00 FWFR LG - 140.00 FWFR MG - 140.00 FWFR HG - 140.00 140.00 HWSAP WASTE - 140.00 HWSAP MO - 140.00 HWSAP LG - 140.00 HWSAP MG - 140.00 HWSAP HG - 140.00 140.00 HWTR WASTE - 140.00 HWTR MO - 140.00 HWTR LG - 140.00 HWTR MG - 140.00 HWTR HG - 140.00 140.00 HWFR WASTE - 140.00 HWFR MO - 140.00 HWFR LG - 140.00 HWFR MG - 140.00 HWFR HG - 140.00 140.00 SOFTWAST WASTE - 140.00 140.00 TRNSWAST WASTE - 140.00 140.00 HARDWAST WASTE 2,206.97 140.00

i arrange it in this format:


RL GKSAP GKSAP GKSAP GKSAP GKTR GKTR GKTR GKTR GKTR GKFR
WASTE LG MG HG WASTE MO LG MG HG WASTE 145.00 - - - - - - - - - - 142.50 - - - - - - - - - - 140.00 - - - - - - - - - - 137.50 - - - - - - - - - - 135.00 - - - - - - - - - - 132.50 - - - - - - - - - - 130.00 - - - - - - - - - - 127.50 - - - - - - - - - - 125.00 - - - - - - - - - - 122.50 - - - - - - - - - - 120.00 - - - - - - - - - -

Ntema, excelledsoftware was asking whether you could attach the workbook itself, not paste in a copy of some of the text. As you can see from looking at the result yourself, what happens when you paste it like that is no help.

However, I can tell you part of what your formula is doing. For the following description let's assume that your formula is in a worksheet named Here; if so, the formula is taking the value in Here!A13 (relative) and looking it up in bench!$C$6:$F$1706. It's assuming that that lookup table is sorted in ascending order; I think if any of the values in col C are out of order, it stops looking at that point. It also stops if it finds a value that is greater than Here!A13, and returns the one just before that, even if it isn't an exact match.

The column it's to pull the data from depends on the contents of row 1 and 2 in Here col C. If the value in $C$1 is one of the 12 Rock Groups, the VLOOKUP pulls the lookup value from bench col D. If what's in Here!$C$! isn't a valid Rock Group, then it checks the value in $C$2, and if it's one of the grade groups then VLOOKUP pulls the lookup value from bench col E. If what's in $C$2 isn't a grade group, either, then VLOOKUP returns the contents of bench col C, in other words the value it found to be a match.

---

Since every row on Here is looking up the same values in $C$1 and $C$2—unless that's a mistake on your part, but I don't suppose it is—allow me to suggest something simpler: Calculate the correct column at the top of the worksheet, and use it for every VLOOKUP in the rest of the sheet. Something like this: In $C$3 putIf you like, you can simplify it a bit without the NOTs:Now make your VLOOKUP just use the column value that you calculated the one time:That is, unless you now see that you're using the wrong columns, or the wrong VLOOKUP range_lookup mode, or something.

hi,

I have managed with PivotTable. It is not a mistake it is how I arrange my tables. See attached file. GK_QTR4_BENCH_BY_BENCH test, PV

I have worked out another VLookup. On my truck factor material where material is "Fresh" I get a #N/A. When i change the material type to either "Soft or Transitional" the result is the correct result...see file October 2013

Attached Files

	October 2013.xls (238.5 KB, 12 views)
	GK_QTR4_BENCH_BY_BENCH test.xlsx (498.3 KB, 12 views)
	pv.xls (68.5 KB, 12 views)