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

Formula

J

jeo varghese

Member
Dear sir

Pls find the attached file

Required formula for the following points

1 Here coloum BA get "yes" or "no" answer from sheet 3 (item id is subject to commission amount or not)
2 coloum BN -if answer is "yes " result is line sales amount(coloumBM) = same

For Example

IF ITEM ID IN BA6 answer in AZ6=Yes

IF AZ6=YES ANSWER IN BN6=150

Please help me to apply formula
Thanks and regards
jeo
 

Attachments

Hi jeo,

I did not understood your requirement. But below find the findings:

1. In your sheet1 column AZ Item Id is text but your look up table on Sheet3 is number, so when you change it to text you will get Yes and No.

2. Put in BN6 =BM6.

Just advise what you are not getting?

Regards,
 
Dear Sir
In coloum AZ - item ID is number - In a look up sheet -sheet No 3 there also item Id is number
In coloum BA where answer should get this item id is subject to commission -yes or No answer.

If answer is YES, same item id sales amount colom BM equals BN

I think its clear Now
please try for me
jeo
 
@jeo varghese

Your column BM already had a value. See the attached file.

And if you wish to calculate commision if there is a YES, what is the %age of commision and %age of what? and if NO than waht you wish to do?



Regards,
 

Attachments

Last edited:
What i have understood is that you want to lookup values in Col AZ in the Table in Sheet 3 and return yes or no from that table. in case it returns yes, you want corresponding value of BM to be present in BN and all this should happen with a formula in BN. If this is correct you can try below formula:

=IFERROR(IF(VLOOKUP(AZ2,Sheet3!$A$2:$B$5,2,FALSE)="YES",BM2,""),"")

Hope that works.
 
@Jeo

In your Sheet1 Tab Column AZ is text format and you have to convert it as Number format so first select the AZ2:AZ17 and right click of mouse button and select the format cells then go to Custom and 000 digits in the Type

when you enter in the Range AZ2:AZ17 = 1 / 2 then it will appear as 001 as you required

please check the file if any problem please inform

Thanks

SP
 

Attachments

Dear Sir,
If i work with new work book this formula it works but when i export raw data from my accounting software to excel
and i try to apply formula result comes NA.
Please find the attached file, i dont know once exported data file and save, whats the result.
please check what i applied formula is correct or not
or What is the reason it shows NA
Looking for your Valuable reply
jeo
 

Attachments

Hey Jeo,

Your Formula is: =IF(AZ6="","",VLOOKUP(AZ6,Sheet3!A2:B5,2,0))

If you check & select lookup value & Press F9 key i.e. AZ6 in above formula then excel will lookup "001" IN sheet 3 where 001 is not found.
=IF(AZ6="","",VLOOKUP("001",Sheet3!A2:B5,2,0))
i.e. the reason for Error #NA

Regards,
AM:)
 
Dear Jeo,

You are again to square 1.

Which formula had solved your problem?

And again you are trying to compare a text with a number in vlookup.

Regards,
 
Dear Sir,

When ever i try to enter the Formula, My Result is #NA ,

I tried both formula

=VLOOKUP(AZ6,Sheet3!A2:B5,2,0)
=IF(AZ6="","",VLOOKUP(AZ6,Sheet3!A2:B5,2,0))

Can you suggest Any solution

jeo
 
You must log in or register to reply here.
Back
Top