Home

Need help with Excel's equivalent to some Google Sheets functions

%3CLINGO-SUB%20id%3D%22lingo-sub-265574%22%20slang%3D%22en-US%22%3ENeed%20help%20with%20Excel's%20equivalent%20to%20some%20Google%20Sheets%20functions%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-265574%22%20slang%3D%22en-US%22%3E%3CP%3EHi%20.%20.%20.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI'm%20looking%20for%20Excel's%20equivalent%20(or%20workaround)%20to%20some%20Google%20Sheets%20functions%3A%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EIn%20Google%20Sheets%2C%20VLOOKUP%20can%20be%20written%20like%20this%3A%3C%2FP%3E%3CP%3E%3DVLOOKUP(A3%2C%7BDefinitions!Z4%3AZ1000%2CDefinitions!C4%3AC1000%7D%2C2%2CFALSE)%3C%2FP%3E%3CP%3ENot%20only%20the%20VLOOKUP%20is%20able%20to%20bring%20two%20separate%20columns%20together%20as%20if%20they%20are%20next%20to%20each%20other%2C%20it's%26nbsp%3Balso%20able%20to%20make%26nbsp%3Bthose%20columns%20arranged%20so%20the%20columns%20on%20the%20right%20can%20be%20put%20as%20if%20it's%20on%20the%20left%20(column%20Z%20is%20physically%20on%20the%20right%20side%20of%20column%20C%2C%20but%20in%20Google%20Sheet's%20VLOOKUP%20function%20it%20can%20be%20made%20so%20the%20column%20Z%20and%20column%20C%20are%20next%20to%20each%20other%2C%20where%20column%20Z%20is%20on%20the%20left%20side%20to%20search%20for%20the%20search%20value%2C%20and%20column%20C%20is%20on%20the%20right%20side%20to%20look%20for%20the%20output%20value).%20Is%20there%20a%20way%20that%20we%20can%20do%20this%20on%20Excel%3F%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThere's%20also%20FILLER%20function%20in%20Google%20Sheets%3A%3C%2FP%3E%3CP%3E%3DFILLER(Definitions!A3%3AA1000%2CDefinitions!C3%3AC1000%26gt%3B%3D100%2CDefinitions!C3%3AC1000%26lt%3B-1537)%3C%2FP%3E%3CP%3EThe%20output%20of%20this%20formula%20is%20a%20list%20generated%20from%20the%20range%20Definitions!A3%3AA1000%26nbsp%3Bof%20the%20table%20A3%3AF1000.%20Only%20data%20in%20rows%20where%20the%20numbers%20in%20column%20C%20%26gt%3B%3D100%20and%20C%20%26lt%3B%3D%201537%20is%20shown%20in%20the%20list.%20Can%20we%20do%20this%20on%20Excel%3F%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EPlease%20advise%2C%20and%20thanks%20in%20advance.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-265574%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EGoogle%20Sheets%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-265608%22%20slang%3D%22en-US%22%3ERe%3A%20Need%20help%20with%20Excel's%20equivalent%20to%20some%20Google%20Sheets%20functions%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-265608%22%20slang%3D%22en-US%22%3ETo%20your%20first%20question%3A%3CBR%20%2F%3E%3DINDEX(Definitions!C4%3AC1000%2CMATCH(A3%2CDefinitions!Z4%3AZ1000%2C0))%3CBR%20%2F%3EYour%20second%20question%20can%20be%20done%20using%20Advanced%20filter%2C%20not%20with%20a%20formula%20.%3CBR%20%2F%3EUnless%20you%20are%20on%20Office%20365%2C%20insider%20fast%20ring%20with%20the%20very%20latest%20new%20features%20released%20just%20this%20month.%20Then%20you%20can%20use%20the%20new%20Filter%20function.%3C%2FLINGO-BODY%3E
Jery Henuhili
New Contributor

Hi . . .

 

I'm looking for Excel's equivalent (or workaround) to some Google Sheets functions:

 

In Google Sheets, VLOOKUP can be written like this:

=VLOOKUP(A3,{Definitions!Z4:Z1000,Definitions!C4:C1000},2,FALSE)

Not only the VLOOKUP is able to bring two separate columns together as if they are next to each other, it's also able to make those columns arranged so the columns on the right can be put as if it's on the left (column Z is physically on the right side of column C, but in Google Sheet's VLOOKUP function it can be made so the column Z and column C are next to each other, where column Z is on the left side to search for the search value, and column C is on the right side to look for the output value). Is there a way that we can do this on Excel?

 

There's also FILLER function in Google Sheets:

=FILLER(Definitions!A3:A1000,Definitions!C3:C1000>=100,Definitions!C3:C1000<-1537)

The output of this formula is a list generated from the range Definitions!A3:A1000 of the table A3:F1000. Only data in rows where the numbers in column C >=100 and C <= 1537 is shown in the list. Can we do this on Excel?

 

Please advise, and thanks in advance.

1 Reply
To your first question:
=INDEX(Definitions!C4:C1000,MATCH(A3,Definitions!Z4:Z1000,0))
Your second question can be done using Advanced filter, not with a formula .
Unless you are on Office 365, insider fast ring with the very latest new features released just this month. Then you can use the new Filter function.
Related Conversations
Extentions Synchronization
Deleted in Discussions on
3 Replies
Tabs and Dark Mode
cjc2112 in Discussions on
38 Replies
flashing a white screen while open new tab
Deleted in Discussions on
14 Replies
Stable version of Edge insider browser
HotCakeX in Discussions on
35 Replies
Security Community Webinars
Valon_Kolica in Security, Privacy & Compliance on
13 Replies