I understand the basics of using the IF function in excel but i have recently been asked to use a worksheet that has this formula in it:
=IF(ISNA(VLOOKUP(I8,'Pt Tracker'!$M$5:$AK$65536,7,FALSE)),"",VLOOKUP(I8,'Pt Tracker'!$M$5:$AK$65536,7,FALSE))
Can somebody explain what the red parts of the formula represent (are looking at)?
Last edited by Shoey263; 07-22-2014 at 06:06 AM.
Isna is a function check the Results whether is "NA" Error and check it is true or false
I8 is the lookup value for which u are trying to find the or extract data from a known range of data.
7 is the column position which starts from known range with respective I8 .
Punnam
Many Thanks, that helps a lot
Would the formula work if i replaced the 7 with a negative number?
I usually prefer to combine the IF and ISNA into one statement when returning the tested statement if there is no error.
Formula:
=IFERROR(VLOOKUP(I8,'Pt Tracker'!$M$5:$AK$65536,7,FALSE)),"")
Two distinct parts here. The green text says if there is an error with the blue text (something not found most likely) then return "" otherwise return the value of the blue formula.
The Blue formula, as Punnam said, looks up the value of cell I8 in the column M5:M65536 to find the FIRST exact match (due to the FALSE at the end). It then moves to the 7th column in the table M5:AK65536 and returns the value of that particular row and column.
Despite the high cost of living, it remains very popular.
Don't forget to mark threads SOLVED when you get an answer and rep all the geniouses that helped you today!
No, it will not. In that case u can go with Index + Match , which works the same as Vlookup.
Punnam
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks