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

Please help me to learn this formula/ how to do this formula ?

R

ryan4646

New Member
Hi all,


this is ryan again. I have a sheet, where in "R4" AND "S4" column there is a formula. when any of the section updated in "c" column, then R4 and S4 automatically getting updated from the second sheet named terms master data.


CAN ANY ONE HELP ME TO LEARN THIS FORMULA ? THE FORMULA IS LOOK LIKE THIS AND I AM UPDATING THE FILE HERE. I AM USING OFFICE 2003


=IF(ISNA(VLOOKUP($C4,'temrs master data'!$B:$D,COLUMN(B$1),0)),"",VLOOKUP($C4,'temrs master data'!$B:$D,COLUMN(B$1),0))


link below:


http://205.196.123.202/u861jajuj1gg/cxa70wqt6o27dd6/master+term.XLS
 
Hi Ryan


=IF(ISNA(VLOOKUP($C4,'temrs master data'!$B:$D,COLUMN(B$1),0)),"",VLOOKUP($C4,'temrs master data'!$B:$D,COLUMN(B$1),0))


Step 1

VLOOKUP($C4,'temrs master data'!$B:$D,COLUMN(B$1),0)

The above formula will find the $C4 value in temrs master data'!$B:$D (data range) and whenever the C4 value is found, then it returns values from COLUMN(B$1) (i.e 1column (B column) value)


step 2

ISNA(VLOOKUP($C4,'temrs master data'!$B:$D,COLUMN(B$1),0)),""

the above formula will check whether the value is found in B column or not


Step 3

IF(ISNA(VLOOKUP($C4,'temrs master data'!$B:$D,COLUMN(B$1),0)),"",VLOOKUP($C4,'temrs master data'!$B:$D,COLUMN(B$1),0))


IF formula will check the ISNA condition (step 2), whether it is true or false, if it is true (the value not found) then it returns "" (blank), otherwise repeat the formula ie step 1


I suggest please search VLOOKUP formula and try to understand

Regards

Vijay
 
Good explanation, Vijay.


Ryan...to help you understand the bold bit in the formula below:

=IF(ISNA(VLOOKUP($C4,'temrs master data'!$B:$D,COLUMN(B$1),0)),"",VLOOKUP($C4,'temrs master data'!$B:$D,COLUMN(B$1),0))

...put the non-bold bit into R4:

=VLOOKUP($C4,'temrs master data'!$B:$D,COLUMN(B$1),0)

...and put some random name - like 'JeffreyWeir' - into C4.


You will see that you will get a #N/A error, because my name isn't in the lookup list. #N/A essentially means 'No Match' or 'Not Applicable'.


Now that we know that, then now let's look again at the entire formula you originally posted:


So what the first bit of that outer formula does - i.e. the IF(ISNA(VLOOKUP($C4,'temrs master data'!$B:$D,COLUMN(B$1),0)) bit - is checks whether the formula returns an error.


If there IS an error, the 2nd part of the IF statement is executed i.e the ,"" bit, which is an empty string.


And if there ISN'T an error, the 3rd bit - i.e. the same VLOOKUP function as was already executed - gets executed again. Because the VLOOKUP is getting done twice whenever there is no error, this is very resource intensive.


Note that in Excel 2007 or later, this is much simplified by the new IFERROR function:

IFERROR(VLOOKUP($C4,'temrs master data'!$B:$D,COLUMN(B$1),0),"")

...which basically tells Excel "If there's an error return a blank, but otherwise if no error return the match".


The IFERROR is much less resource intensive. Google IFERROR or search this forum for more info.
 
hi vijay,


thanks for your explanation. I have a little query that, if the first formula can do all the job, why there is a second formula to check. this one is making me think so much.In the vlookup i usually use one equation, though i am a primary user. and there is a (B$1),0)), zero in the formula. can you help me why is that?


hi jeffrey,


yes, i heard that. but i have to use 2003. thanks for the advise mate.
 
Hi Ryan. I edited my post above to make it a bit clearer. Basically without the larger function, you would get an error in cases where there is no match.
 
Hi jeffrey,


understood. so the second formula is using not to show N/A. Just one more help if u dont mind, as you know that vlookup has its own format that auto show up when we click the formula bar and select vlookup formula by mouse, select the criteria by mouse and get the desired result, is there any way to do this ISNA function to be done by Mouse :p ?? it looks like i have type the entire formula, so there is high chance of typo. Hope you understand the newbie problem.
 
Hi Ryan


Basically, the second Formual ISNA is used to remove #N/A error values when the lookup value (C$$ value) not found in the range. To make better formatting, we used ISNA formuala along with IF formula which will check error values, if error value found, then the formula will convert the error values into blank values (""). There is not an compulsory to use this, thats totally user dependable.


and COLUMN() formula will always give numerical values, if you simply use VLOOKUP then it should be VLOOKUP($C4,'temrs master data'!$B:$D,1,0), to make reference stability we use COLUMN() instead of numeric data, if you simply type 1 instead of COLUMN() and if you want to drag the formula to another cells, it gives error


So to dynamically set the range reference, we would use COLUMN() and ROW() formulas


Regards

Vijay
 
Ryan


Firstly, van you goto File, Options, Advanced and check that Show Function Screen Tips is Checked


As you start typing a formula it should show you the format

As you type =isn

Excel will show available options

The available functions decrease as you add more text to the formula

Use the Mouse to select the formula/ranges as appropriate

use the Tab key when a Range etc is right to move to the next part of the formula
 
hi hui,


I am using office 2003. so the shortcut is not the way you mentioned.

i am typing in the insert function option but there is no ISNA function showing up
 
You must log in or register to reply here.
Back
Top