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

[Jery Henuhili]

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.

[#2]

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.