Select your Top Menu from wp menus

Can VLOOKUP Lookup Duplicates? Yes! Helper Column or Array Formula?

This transcription is about “Excel Vlookup” the formula VLOOKUP is used to check duplicates. This array formula will solve to deal with a large data to find duplicate.

YOU WANT TO DOWNLOAD THIS WORKBOOK EXO METRIC IT FOLLOW ALONG CLICK ON THE LINK BELOW THE VIDEO OH HERE IS A COMMON PROBLEM WITH VLOOKUP WE ARE TRYING TO LOOK UP THE DIFFERENT SERVER NAMES FROM THIS DATA SET BUT EACH SERVER HAS A DIFFERENT EVENT BUT LOOK AT WHAT VLOOKUP DOES IT LOOKED UP FOR EVENT AND EVENT IT ONLY LOOKED UP THE FIRST ONE AND THEN WHEN WE GOT TO THE NEXT SERVER THERE ARE THREE INSTANCES WITH THREE EVENTS BUT WHAT DID IT DO IT LOOKED UP THIS AND IT LOOKS LIKE IT’S ONLY RETURNING THE FIRST ONE WELL THAT’S HOW VLOOKUP IS PROGRAM.

WHEN YOU’RE DOING IN THE RANGE LOOKUP EXACT MATCH WITH A FALSE OR A ZERO IT IS PROGRAMMED TO ONLY TAKE THE FIRST ONE WHEN IT SEES DUPLICATE SO WHAT IS THE SOLUTION WELL CLEARLY SERVER NAME AN EVENT SERVER NAMING EVENT THOSE TWO THINGS TOGETHER CREATE A UNIQUE IDENTIFIER SO ONE OF THE WAYS TO SOLVE THIS IS TO CREATE A HELPER COLUMN AND SIMPLY TAKE SERVER NAME AND JOIN IT USING THE AMPERSAND SHIFT TO EVENT NOW WE NO LONGER ARE GOING TO HAVE DUPLICATES IN OUR FIRST COLUMN WHEN I DOUBLE CLICK AND SEND THIS DOWN I HAVE THE SERVER NAME EVENT SERVER NAME EVENT NOW I CAN COME OVER HERE EQUALS VLOOKUP AND THE LOOKUP VALUE HOW ARE WE GOING TO JOIN SERVER NAME AND EVENT SIMPLY CLICK ON SERVER NAME AND WRITE IN OUR LOOKUP VALUE USE THE AMPERSAND TO JOIN WITH THE EVENT THAT WAY RIGHT HERE ON OUR LOOKUP VALUE IF I CLICK ON THAT AND HIT F YOU CAN SEE BOOM.

WE HAVE A SINGLE ITEM WE CAN LOOK UP AND TRY AND MATCH IN OUR FIRST COLUMN OF OUR VLOOKUP TABLE CONTROL Z COME TO THE END COMMA AND HERE’S OUR NEW TABLE FIRST COLUMN ALL THE WAY TO THE FOURTH COLUMN STATUS I’M GOING TO HIT THE F KEY COMMA COLUMN INDEX THE FOURTH COLUMN IS STATUS AND THAT HAS THE ITEMS WE WANT TO GO AND GET AND BRING BACK TO THE CELL SO I’M GOING TO PUT A FOUR KALA IN EITHER FALSE OR ZERO FOR EXACT MATCH CLOSE PARENTHESES CONTROL ENTER DOUBLE CLICK AND SEND IT DOWN THAT IS AMAZING F VLOOKUP HAD NO PROBLEM BECAUSE NOW IT’S LOOKING UP SERVER NAME AND EVENT FINDING A MATCH IN THAT FIRST COLUMN NOW OCCASIONALLY YOU CAN’T ADD A HELPER COLUMN TO YOUR TABLE AND YOU WANT A SINGLE CELL FORMULA WELL WE’RE NOT GOING TO USE VLOOKUP BY THE WAY I HAVE A VIDEO EXCEL METRIC GOES OVER DIFFERENT WAYS TO SOLVE THIS PROBLEM AND I SHOW YOU A BUNCH OF WAYS THERE THE MAIN WAY IS TO DO EITHER HELPER COLUMN AND VLOOKUP OR WE’RE GOING TO SEE THE OTHER MAIN WAY AND IT DOESN’T INVOLVE VLOOKUP WE’RE GOING TO USE TWO NEW LOOKUP FUNCTIONS AND THE FIRST ONE IS INDEX NOW INDEX HAS AN ARRAY AND THOSE ARE ONLY THE ITEMS THAT YOU WANT TO GO AND GET AND BRING BACK TO THE CELL I’M GOING TO HIT F COMMA AND THEN ALL IT NEEDS IS A ROW NUMBER.

WELL NOTICE I ONLY HAVE THE OPTIONS OR SO SOMEHOW IN ROW NUMBER OF INDEX I NEED TO LOOK UP THESE TWO ITEMS AND FIGURE OUT WITHOUT THIS HELP OR COLUMN WHAT THE RELATIVE POSITION OR WHICH ROW NUMBER IT IS SO NO PROBLEM WE’RE GOING TO USE OUR SECOND LOOKUP FUNCTION CALLED MATCH CAN LOOK SOMETHING UP AND TELL YOU THE RELATIVE POSITION OF AN ITEM IN A LIST OR IN OUR CASE THE ROW NUMBER I’M GOING TO DO THE SAME TRICK AS I DID IN VLOOKUP THERE’S THE SERVER NAME I JOIN IT TO THE EVENT COMMA WELL WAIT A SECOND LOOK UP A RATE IF I’M NOT ALLOWED TO HAVE THAT I’M JUST GOING TO CREATE IT RIGHT IN MY FORMULA I HAVE JUST HIGHLIGHTED THE ENTIRE SERVER NAME COLUMN AND I’M GOING TO DO.

WHAT’S CALLED AN ARRAY OPERATION YES THAT’S THE JOIN SYMBOL IT’S THE SAME JOIN SYMBOL WE USE RIGHT HERE BUT HERE WE HAD ONE ITEM JOIN TO ANOTHER ITEM THIS IS CALLED AN ARRAY OPERATION BECAUSE WE’RE NOT JOINING JUST ONE ITEM TO ANOTHER THAT’S WHOLE COLUMN AND I’M GOING TO JOIN IT TO THE EVENT COLUMN NOW I CAN CLICK ON LOOKUP ARRAY AND EVALUATE THIS WITH THE F KEY TO PROVE TO MYSELF THAT I ACTUALLY DID I JOINED EACH INDIVIDUAL ITEM AND CREATED A WHOLE-NEW HELPER COLUMN IN MY FORM AX NOW CTRL Z TO THINGS ONE I NEED TO LOCK THIS SO I’M GOING TO HIGHLIGHT THAT BECAUSE I FORGOT I’M GOING TO HIT THE F KEY AND THEN RIGHT HERE F NOW THIS IS A SPECIAL TYPE OF OPERATION IT’S CALLED AN ARRAY OPERATION RIGHT AND WE’RE ACTUALLY GOING TO HAVE TO TELL EXCEL THAT THIS IS A SPECIAL TYPE OF ARRAY OPERATION BY USING THE KEYSTROKE CTRL SHIFT AND ENTER WHEN WE ENTER THE FORMULA IN THAT LOOKUP ARRAY REQUIRES THIS SPECIAL KEYSTROKE OR ELSE THAT WE WILL GET AN ERROR NOW COMMA MATCH HAS THE SAME EXACT SO WE’RE GOING TO PUT BECAUSE WE’RE DOING EXACT MATCH CLOSED PARENTHESIS AND BOOM THERE IT IS ROW NUMBER THAT WHOLE MATCH THING IF I HIT F DELIVERS A AS I COPY DOWN IT’LL CHANGE THE RELATIVE POSITION CTRL Z INDEX HAS WHAT IT NEEDS SO I COME D IN CLOSED PARENTHESIS NOW WE HAVE TO USE OUR KEYSTROKE CONTROL SHIFT AND ENTER THAT’S US TELLING EXCEL THAT WE MADE AN ARRAY CALCULATION YOU IMMEDIATELY.

LOOK UP TO THE FORMULA BAR YOU COULD SEE THOSE CURLY BRACKETS THAT’S EXCEL TELLING YOU IT UNDERSTOOD THAT THIS WAS AN ARRAY OPERATION LET’S JUST SEE WHAT HAPPENS WHAT IF I HIT F AND THEN ENTER THAT’S THE ERROR YOU’LL GET THAT ERROR SAYS HEY YOU FORGOT TO USE THE SPECIAL KEYSTROKE CTRL SHIFT AND ENTER NOW I CAN DOUBLE CLICK AND SEND IT DOWN AND BOOM A SINGLE CELL FORMULA TO LOOKUP TWO ITEMS WITHOUT OUR HELPER COLUMN SO THE TWO PREFERRED METHODS IS EITHER THAT SINGLE CELL FORMULA OR THE HELPER COLUMN BUT I WANT TO SHOW YOU ONE OTHER THIRD OPTION AND I’M GOING TO COPY THIS IN EDIT MODE ESCAPE COME OVER HERE AND PASTE IT CTRL V AND WHEN WE DID THIS ARRAY OPERATION RIGHT HERE ON THE LOOKUP ARRAY THAT ARGUMENT REQUIRES CONTROL SHIFT ENTER OR IT DOESN’T CALCULATE CORRECTLY BUT WATCH THIS IF WE TAKE THAT WHOLE ARRAY OPERATION AND SIMPLY PUT IT INSIDE A SECOND INDEX THERE ARE FIVE FUNCTIONS IN EXCEL WITH ARGUMENTS THAT CAN HANDLE ARRAY OPERATIONS WITHOUT A SPECIAL KEYSTROKE AND THE ARRAY ARGUMENT CAN HANDLE ARRAY OPERATIONS WITHOUT CONTROL SHIFT ENTER.

SO THE TRICK HERE IS WE WANT TO GET ALL OF THE ROWS SIMULTANEOUSLY SO I TYPE A COMMA AND FOR ROW NUMBER I LEAVE IT EMPTY BY LEAVING THE ROW NUMBER ARGUMENT EMPTY IT TELLS INDEX TO GET ALL OF THE ROWS SIMULTANEOUSLY NOW LOOK AT THIS THAT MEANS WE’RE SIMPLY USING INDEX TO HOUSE THAT ARRAY OPERATION AND IT’S SITTING INSIDE THE LOOKUP ARRAY AND IT WILL NO LONGER REQUIRE CONTROL SHIFT ENTER IF WE F YOU CAN SEE IT GIVES US THE SAME EXACT SIMULATED HELPER COLUMN INSIDE OUR FORMULA CONTROL Z BUT WHAT I SIMPLY CONTROL UNDER I DIDN’T USE CONTROL SHIFT ENTER THERE’S NO CURLY BRACKETS AND IT WORKS LIKE A CHARM SO IF YOU WANT TO USE INDEX MATCH INDEX OR PROBABLY THIS IS THE ONE THAT I WOULD USE IF I WERE DOING A SINGLE CELL FORMULA BUT MOST OF THE TIME IT’S CERTAINLY FAST AND EASY TO JUST USE VLOOKUP WITH AN EXTRA HELP OR COLUMN ALL RIGHT WE’LL SEE YOU NEXT VIDEO.

Video Link

About The Author

Related posts

Leave a Reply

Your email address will not be published. Required fields are marked *