• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Lookup, Index

R

Rik Smith

New Member
Hey Guys,

Column C contains the main criteria “enduse”

Sheet 2 contains a cross classification with “enduse” an “NAEnduse”

Sheet 1 also has “NAEnduse” and “NAAltEnduse”

I want to create a column called “Final NAenduse”


It will use the “enduse” with the cross classification on sheet 2

So if “enduse” is 51 the cross classification is saying 3 hence “Final NAenduse” will be 3

But it’s possible for the “NAEnduse” and “NAAltEnduse” to deviate from the cross classification

When this happens the “NAEnduse” and “NAAltEnduse” takes priority

In this case the “NAEnduse” on sheet 1 will take priority and be used for the “Final NAenduse”

See highlighted area for diversion

Column G is the expected result in “Final NAenduse” for the entire worksheet.

Thanks
 

Attachments

  • Comflow Test 260215.xlsx
    316.1 KB · Views: 10
Formula in F2:
=IF(OR(VLOOKUP(C2,Sheet2!$A$1:$C$22,3,)=D2,VLOOKUP(C2,Sheet2!$A$1:$C$22,3,)=E2),VLOOKUP(C2,Sheet2!$A$1:$C$22,3,),D2)

If you don't mind having a helper column, would be actually more efficient to have col F with formula:
=VLOOKUP(C2,Sheet2!$A$1:$C$22,3,)
and then final column have formula:
=IF(OR(F2=D2,F2=C2),F2,C2)
 
Thanks Luke but i failed to mention one outcome i would like,

refer to row 19 where the enduse is 52 which would cross reference to 3

the NAAlt_Enduse is 3

In this senario i would like the 'NAAlt_Enduse' to hold in the “Final NAenduse” hence i would be 3
 
Ah ha, I see now that D2 and E2 have lots of trailing space after the number. See if this fixes it:
=IF(OR(VLOOKUP(C3,Sheet2!$A$1:$C$22,3,)=TRIM(D3),VLOOKUP(C3,Sheet2!$A$1:$C$22,3,)=TRIM(E3)),VLOOKUP(C3,Sheet2!$A$1:$C$22,3,),TRIM(D3))
 
Hi Rik ,

You may have got your solution from Luke , but I think you should relook at what is being done on the tab.

1. You have an End Use on Sheet1. I assume this is manually entered.

2. Looking up this End Use code in the Master Table on Sheet2 will result in an NA End Use code.

3. You say that you have an NA End Use ( column D ) and an NAAlt_EndUse ( column E ) in Sheet1. I assume these are both manually entered.

4. You say that in certain cases these two manually entered data should override the looked up value of NA End Use.

I can understand that the manually entered value in column D may override the looked up value ; what is the purpose of the manually entered value in column E ? What part does it play in the Final End Use code ?

When does the value in column D play a role , and when does the value in column E play a role in determining the final result in column F ?

Narayan
 
NARAYANK991 and Luke M

Column D and E are system generated from a master file in the back-end using SAS.

Sheet 2 is basically a guide as to what would normally happen

But it deviates in a few instances such a row 10 where the enduse is 41 which would normally be 1 but i want it to be 7 because column D and E takes priority over sheet 2.

Luke got that with the first formula but i lost it in some instances with the second formula

So the purpose of column E is that if enduse is 42 the formula should chose 1 if it is in D or E if not it should take the value of D

Example row 11 enduse is 41 and 1 is not an option in D or E so 7 is taken.

row 102 enduse 51, which based on sheet 2 should be 3, and 3 is in column E i would like the 3 to be chosen

Hope this clears up the misconception

Thanks guys i'm learning so much doing this exercise
 
Grr, I copied the wrong cell last time. Formula I posted was from row 3, causing all formulas to be off one row. In F2, formula should be
=IF(OR(VLOOKUP(C2,Sheet2!$A$1:$C$22,3,)=TRIM(D2),VLOOKUP(C2,Sheet2!$A$1:$C$22,3,)=TRIM(E2)),VLOOKUP(C2,Sheet2!$A$1:$C$22,3,),TRIM(D2))

This appears to align now with your expected results.
 
Hi ,

If the data in columns C , D and E is all available from an external source , then what is the use of the master table in Sheet2 ? Why have it at all ?

Narayan
 
If the result can be generated without it, it can be discarded.

But its the only way i can see for us to know if for instance the enduse is 52 it should always take 3 if it is an option in column D or E

If 3 is not an option in D or E then take the option in D whatever it is

As can be seen in sheet 2 52 is given an NAEnduse of 3
 
Hi Rik ,

I am sorry but I am not able to understand ; I hope Luke does and can help.

As far as I am concerned , I understand that if you look up the End Use code , available in column C on Sheet1 , in the master table on Sheet2 , you can get an NAEndUse code.

What I want to know is what use is to be made of this ?

If the output in column F ( Final EndUse ) is to be derived from the data in column D ( NAEndUse ) and column E ( NAAlt_EndUse ) on Sheet1 , then what is the necessity for having the master table ?

All the explanation so far has not explained this.

Narayan
 
Hey Guys,

This formula actually works perfect

Grr, I copied the wrong cell last time. Formula I posted was from row 3, causing all formulas to be off one row. In F2, formula should be
=IF(OR(VLOOKUP(C2,Sheet2!$A$1:$C$22,3,)=TRIM(D2),VLOOKUP(C2,Sheet2!$A$1:$C$22,3,)=TRIM(E2)),VLOOKUP(C2,Sheet2!$A$1:$C$22,3,),TRIM(D2))

This appears to align now with your expected results.
Best Regards,
Luke M
=======
 
You must log in or register to reply here.
Back
Top