*When you need to find data in your sheet that corresponds to a certain key record, it is usually Google Sheets VLOOKUP you turn to. But there you go: VLOOKUP slaps you with limitations almost immediately. That's why you'd better increase the resources for the task by learning INDEX MATCH.*

INDEX MATCH in Google Sheets is a combination of two functions: INDEX and MATCH. When used in tandem, they act as a better alternative for Google Sheets VLOOKUP. Let's find out their capabilities together in this blog post. But first, I'd like to give you a quick tour of their own roles in spreadsheets.

## Google Sheets MATCH function

I'd like to start with Google Sheets MATCH because it's really simple. It scans your data for a specific value and returns its position:

*search_key*is that record you're looking for. Required.*range*is either a row or a column to look in. Required.Note. MATCH only accepts one-dimensional arrays: either row or column.

*search_type*is optional and defines if the match should be exact or approximate. If omitted, it is 1 by default:*1*means the range is sorted in ascending order. The function gets the largest value less than or equal to your*search_key*.*0*will make the function look for the exact match in case your range is not sorted.*-1*hints that records are ranked using descending sorting. In this case, the function gets the smallest value greater than or equal to your*search_key*.

Here's an example: to get a position of a certain berry in the list of all berries, I need the following MATCH formula in my Google Sheets:

`=MATCH("Blueberry", B1:B10, 0)`

## Google Sheets INDEX function

While MATCH shows where to look for your value (its location in the range), Google Sheets INDEX function fetches the value itself based on its row and column offsets:

*reference*is the range to look in. Required.*row*is the number of rows to offset from the very first cell of your range. Optional, 0 if omitted.*column*, just like*row*, is the number of offset columns. Also optional, also 0 if omitted.

If you specify both optional arguments (row and column), Google Sheets INDEX will return a record from a destination cell:

`=INDEX(A1:C10, 7, 1)`

Skip one of those arguments and the function will get you the entire row or column accordingly:

`=INDEX(A1:C10, 7)`

## How to use INDEX MATCH in Google Sheets — formula examples

When INDEX and MATCH are used together in spreadsheets, they are at their mightiest. They can absolutely substitute Google Sheets VLOOKUP and fetch the required record from a table based on your key value.

### Build your first INDEX MATCH formula for Google Sheets

Suppose you'd like to get the stock info on cranberry from the same table I used above. I only swapped columns B and C (you'll find out why a bit later).

- Now all berries are listed in column C. Google Sheets MATCH function will help you locate the exact row of the cranberry:
*8*`=MATCH("Cranberry", C1:C10, 0)`

- Put that whole MATCH formula to a
*row*argument in the INDEX function:`=INDEX(A1:C10, MATCH("Cranberry", C1:C10, 0))`

This one will return the entire row with cranberry in it.

- But since all you need is the stock info, specify the number of the lookup column as well:
*3*`=INDEX(A1:C10, MATCH("Cranberry", C1:C10,0), 2)`

- Voila!
- You can go further and give up that last column indicator (
*2*). You won't need it at all if you use only the lookup column (*B1:B10*) rather than the entire table (*A1:C10*) as the first argument:`=INDEX(B1:B10, MATCH("Cranberry", C1:C10, 0))`

Tip. A more convenient way to check the availability of various berries would be to place them in a drop-down list (*E2*) and refer your MATCH function to the cell with that list:

`=INDEX(B1:B10, MATCH(E2, C1:C10, 0))`

Once you select the berry, the related value will change accordingly:

### Why INDEX MATCH in Google Sheets is better than VLOOKUP

You already know that Google Sheets INDEX MATCH looks your value up in a table and returns another related record from the same row. And you know that Google Sheets VLOOKUP does exactly the same. So why bother?

The thing is, **INDEX MATCH** has **some major advantages** over VLOOKUP:

**Left-side lookup is possible**. I changed the columns places earlier to illustrate this one: INDEX MATCH function in Google Sheets can and does look to the left of the search column. VLOOKUP always searches the very first column of the range and looks for matches to its right — else, it gets only #N/A errors:**No messed up references when adding new columns and moving existing ones.**If you add or move columns, INDEX MATCH will reflect the changes automatically without meddling in the result. Since you use column references, they are instantly adjusted by Google Sheets:Go ahead and try to do this with VLOOKUP: it requires the order number rather than cell references for a lookup column. Thus, you'll just end up getting the wrong value because another column takes the same place — column

*2*in my example:**Considers text case**when necessary (more on this right below).- Can be used for vertical lookup based on multiple criteria.

I invite you to look at the last two points in detail below.

### Case-sensitive v-lookup with INDEX MATCH in Google Sheets

INDEX MATCH is a go-to when it comes to case-sensitivity.

Supposing all berries are being sold in two ways — loose (weighed at the counter) and packed in boxes. Hence, there are two occurrences of each berry written in different cases in the list, each with its own ID that also vary in cases:

So how can you look up the stock info on a berry sold in a certain way? VLOOKUP will return the first name it finds no matter its case.

Luckily, INDEX MATCH for Google Sheets can do it correctly. You'll just need to use one additional function — FIND or EXACT.

#### Example 1. FIND for case-sensitive Vlookup

FIND is a case-sensitive function in Google Sheets which makes it great for case-sensitive vertical lookup:

`=ArrayFormula(INDEX(B2:B19, MATCH(1, FIND(E2, C2:C19)), 0))`

Let's see what happens in this formula:

- FIND scans column C (
*C2:C19*) for the record from*E2*(*cherry*) considering its letter case. Once located, the formula "marks" that cell with a number —*1*. - MATCH searches for this mark —
*1*— in the same column (*C*) and hands the number of its row to INDEX. - INDEX comes down to that row in column B (
*B2:B19*) and fetches the required record to you. - When you finish building the formula, press Ctrl+Shift+Enter to add ArrayFormula at the beginning. It is required because without it FIND won't be able to search in arrays (in more than one cell). Or you can type '
*ArrayFormula*' from your keyboard.

#### Example 2. EXACT for case-sensitive Vlookup

If you replace FIND with EXACT, the latter will look for records with the exact same characters, including their text case.

The only difference is that EXACT "marks" a match with *TRUE* rather than number *1*. Hence, the first argument for MATCH should be *TRUE*:

`=ArrayFormula(INDEX(B2:B19, MATCH(TRUE, EXACT(E2, C2:C19), 0)))`

### Google Sheets INDEX MATCH with multiple criteria

What if there are several conditions based on which you'd like to fetch the record?

Let's check the price of the *cherry* that is being sold in *PP buckets* and is already *running out*:

I arranged all the criteria in the drop-down lists in column F. And it is Google Sheets INDEX MATCH that supports multiple criteria, not VLOOKUP. Here's the formula you will need to use:

`=ArrayFormula(INDEX(B2:B24, MATCH(CONCATENATE(F2:F4), A2:A24&C2:C24&D2:D24, 0),))`

Don't panic! :) Its logic is actually quite simple:

*CONCATENATE(F2:F4)*combines all three records from cells with criteria into one string like this:*CherryPP bucketRunning out*This is a

*search_key*for MATCH, or, in other words, what you're looking for in the table.*A2:A24&C2:C24&D2:D24*constitute a*range*for the MATCH function to look in. Since all three criteria take place in three separate columns, this way you kind of combine them:*CherryCardboard trayIn stock*

*CherryFilm packagingOut of stock*

*CherryPP bucketRunning out*

etc.- The last argument in MATCH —
*0*— makes it possible to find the exact match for*CherryPP bucketRunning out*among all those rows of combined columns. As you can see, it's in the 3rd row. - And then INDEX does its thing: it fetches the record from the 3rd row of column B.
- ArrayFormula is used to allow other functions to work with arrays.

Tip. If your formula doesn't find a match, it will return an error. To avoid that, you can wrap this entire formula in IFERROR (make it the first argument) and enter whatever you want to see in a cell instead of errors as a second argument:

`=IFERROR(ArrayFormula(INDEX(B2:B27, MATCH(CONCATENATE(F2:F4), A2:A27&C2:C27&D2:D27, 0),)), "Not found")`

## Better alternative to INDEX MATCH in Google Sheets — Filter and Extract Data

Whatever lookup function you prefer, VLOOKUP or INDEX MATCH, there's a better alternative to them both.

Filter and Extract Data is a special add-on for Google Sheets designed to:

- lookup without formulas
- lookup in all directions
- search by multiple conditions for different data types:
*text, numbers, dates, time,*etc. - fetch several matches, as many as you need (providing there are as many of them in your table, of course)

The interface is straightforward, so you won't have to doubt whether you're doing everything correctly:

- Select source range.
- Set the number of matches and columns to return.
- Fine-tune the conditions using the predefined operators (
*contains, =, not empty*,*between*, etc.).

You will also be able to:

- preview the result
- decide where to place it
- and how: as a formula or just values

Don't miss out on this opportunity to check the add-on. Go ahead and install it from Google Workspace Marketplace. Its tutorial page will explain every option in detail.

We also prepared a special instructional video:

## Vlookup multiple matches from multiple sheets & update the related data – Merge Sheets add-on

The next level would be to not just pull the matches but to look them up in multiple sheets at once and update the related values in the neighboring columns of your main table.

The quickest way to do that is using the Merge Sheets add-on for Google Sheets.

Tip. You will find more ways to match & merge your Google Sheets data in this article.

There are 5 steps where you:

- Select your main sheet (the one where you'd like to pull the data to).
- Select all your lookup sheets (those to match with the main sheet and pull the data from):
- Identify matching columns.
- Specify the columns to update (in the main sheet) or even add (from the lookup sheet(s)):
- Tweak additional options such as highlight changes, update only blank cells, etc.

Watch the demo video with the add-on in action below or look through its tutorial page.

Though the video doesn't feature adding multiple sheets, the latest update brings this option to your spreadsheets. Install Merge Sheets from Google Marketplace and prove me right ;)

See you in the comments below or in the next article ;)

## 79 comments

Doe not work especially when you want to use it witch real world stock data

=INDEX(A25:AA510, MATCH(M3, A25:A510,1), 26)

Hello Glenn,

For me to be able to help you, please specify what exactly your formula returns.

I would like to utilise the Import Match formulas to populate from another spreadsheet altogether. I was looking at using the importrange function with this but everytime I try I return varying errors.

Any advice on how this can be overcome would be greatly appreciated.

Thanks,

Liam

Hello Liam,

Please provide the formula you're trying to use and specify what error exactly (upon hovering a cell with the formula) it returns.

I am trying to make it so that my formula can pull multiple responses from my main sheet. I have two columns, one to list the items and the second to mark if I am "out" of said item. After a few attempts at messing around with the function I've only been able to get it to pull the name of the last item on the first list that is "out."

Is there a way to write it so that this formula will return all of the items on my list that are marked as such?

Hi! I’m sorry, but your description doesn’t give me a complete understanding of your task. Correct me if I’m wrong, to get a list of values in the column according to the criterion, write this formula in cell D3:

=ARRAY_CONSTRAIN(ARRAYFORMULA(IFERROR(INDEX($A$3:$A$13, SMALL(IF(D$2=$B$3:$B$13, ROW($A$3:$A$13)-2,""), ROW()-2)),"")), 1, 1)

A - column of values

B - criteria column

D2 -cell with criterion

This did work, but I had to copy the formula down the column to get multiple responses. Thankfully it didn't repeat data so I can use this but I was really wanting it to auto-populate the entire list without needing to copy the formula multiple times.

Sorry my original post wasn't as clear an explanation as what I had in my head, I essentially have two lists on my first sheet that look like this:

Column A | Column B | Column C | Column D ... and so on

Product 1 | Out | Product 5 | Out

Product 2 | Out | Product 6 | Low

Product 3 | Low | Product 7 | Out

Product 4 | Good | Product 8 | Good

My goal is for the second sheet to only show me the items that are marked at as "Out" in Column A, C, and so forth. I prefer the data from each set stays separate so on my end retyping the formula once for each is fine.

Hi! If I understand your task correctly, try to use FILTER formula:

=FILTER(A1:B5,B1:B5="Low")

Thank you, that works perfectly! I definitely need to stop overcomplicating things in my head when the solution ends up being so simple :)

Good day,

I am trying to add a column into a Google Sheet. I will use an example.

I have 2 sheets lets name them Sheet A and Sheet B. Both of these sheets have different information shared between each other and also tabs using all kind of Vlookup(s) etc. etc.

Both these sheets are used to load and retrieve data from.

In Sheet A I need to add a Colum. However when I add the column in this Sheet A, the Tab needs to retrieve information from Sheet B. It retrieves some data but it messes up the information in the cells which seems that it moved calculations or the obvious reason is that the column where the information are retrieved moved. But this tell me that the formulas (cross referencing to different tabs) did not automatically update when I added the Colum in Sheet A. I expected that the Google Sheet will as like a Excel spreadsheet automatically update the formulas in a column or cell when it was moved.

As I thought the formulas in both sheets will update and as this is not happening, I am lost on how to fix the sheet to allow the adding of the column without "breaking" the lookup and other formulas.

The Sheet B uses "importrange" to retrieve information from Sheet A but the information in one of the tabs inside Sheet B is then screwed up when I add the Column in Sheet A.

If I remove the column from Sheet A all data restores back. How can I add the Column in Sheet A without messing up the information in Sheet B's one tab?

Thank you for any advise.

Kind regards

Werner

Good day, Werner.

Ranges adjust themselves automatically if they are written as pure ranges if I may say so. For example, in VLOOKUP the 2nd arguments requires such a range:

=VLOOKUP($A$2,Sheet2!B1:C30,2)

It will adjust itself accordingly whenever you add other columns/rows to Sheet2.

But in IMPORTRANGE you have to write the range as a text string inside double quotes:

=IMPORTRANGE(spreadsheet_url,"Sheet1!A1:D100")

Such records won't adjust themselves automatically since they are text strings. You will have to edit them manually after interfering with their source data.

Hi

I want to make the v lookup from another sheet which are having dates in rows and need to have excet date in base sheet

Hi Ashish,

Sorry, I don't understand what the last part of your task mean: "need to have excet date in base sheet". Have you tried INDEX MATCH from this article?

Natalia, thanks for a great explanation. Crystal clear and I like your humor.

Thank you for your lovely words, Ray! :)

XLOOKUP should be mentioned, or even featured, in this article. It solves many of the VLOOKUP limitations, such as searching to the left.

how can i use Index and match to select particular information from different file ?

Hello!

Use IMPORTRANGE function to import data from another Google sheet. Then use this data in INDEX MATCH or VLOOKUP function.

Hi Natalia,

This solution is brilliant, thanks so much for sharing.

It almost works for what I need, The word I'm looking for is contained as part of a phrase in the rows for the range.

MATCH has to find exactly the word exactly and not as part of the string, right?

Is there a way to modify this to find the searched word inside a phrase?

Appreciate your feedback, Ramon! :)

You can try incorporating wildcard characters or try Multiple VLOOKUP Matches and see how it handles partial matches.

This was so brilliant!!! Thanks so much. Now I have an issue with sorting static and dynamic data on a tab. Could you please do a tutorial on this as well?

Appreciate your feedback, Chris!

Would you mind sharing the data you'd like to sort with us: support@apps4gs.com?

Note.We keep that Google account for file sharing only and don't monitor its Inbox. Please do not email there. Once you share the file, just confirm by replying to this comment.Please include an extra sheet illustrating the result you expect to get. I kindly ask you to shorten the tables to 10-20 rows.

It will help me understand your use case. Perhaps, I'll be able to suggest a solution right away.

Hi!

I have a sheet with job numbers with multiple bills, and each bill has a status of Paid, Unpaid, or Not Billed.

I need to combine each job number's statuses into another sheet, but uniquely. That is, if one job number has 3 bills paid and 2 bills unpaid, then the result should be "Paid, Unpaid". I would even take these in separate columns (I can combine them later).

I've tried INDEX MATCH, and even separated out the three statuses into three different columns on the source sheet, but the formula only populates the column of the first status it hits.

Any suggestions?

Hi Ben,

For me to be able to help you, please consider sharing a small sample spreadsheet with us (support@apps4gs.com) with your source data and the result you expect to get. I kindly ask you to shorten the tables to 10-20 rows.

Note.We keep that Google account for file sharing only and don't monitor its Inbox. Please do not email there. Once you share the file, just confirm by replying to this comment. Thanks!I need help with the following:

Sheet 1 = google form responses with email address and scores

Sheet 2 = Column A = full name and column B = their email address

Sheet 3 = Column A = full name and column B = scores

Question: What formula do I do so that if sheet 3's column A (their full name), matches sheet 1's collected email address, then sheet 3's column B will copy their score from Sheet 1?

Hello Val,

For the Google Sheets formula to match and merge the data, you need to match names with names and email addresses with email addresses, not email addresses with names. In this case, INDEX MATCH from this articel is perfect for the task. Have you tried it?

Hello,

I have a sheet which contains a table that retrieves fleet numbers of vehicles from another sheet using importrange function. I also have a index table on the same sheet that's made up of 7 columns which contains all the fleet numbers and a row of headers which identifies the type of fleet. I need to write a formula to match fleet number from the index table and return the header value of the clomn the it's found in. I tried using index and match functions as well as the vlookup. Can you help please

Hello Ridvan,

For me to be able to help you better, please consider sharing a small sample spreadsheet with us (support@apps4gs.com) with the result you expect to get. I kindly ask you to shorten the tables to 10-20 rows.

Note.We keep that Google account for file sharing only and don't monitor its Inbox. Please do not email there. Once you share the file, just confirm by replying to this comment. Thanks!HI,

I have two spread sheets. One has multiple columns including product SKU's and prices. The second has 2 columns with SKU's and new prices. I'd like to search in the second spreadsheet for a SKU in the first spreadsheet and return the new price value from the second spreadsheet into the price associated with the SKU from the first sheet. Then I'd like to move down the column in the first spreadsheet replacing each associated price with the new price in sheet 1.

I could combine the two spreadsheets if that makes it easier. I think Index Match is the best way to achieve this copying the formula down the column but it is beyond me. Thanks for any help.

-Frank

Hi Frank,

Yes, INDEX MATCH is perfect for fetching the data from one table into another based on common records. You can try recreating the formula step by step by reading the blog post above.

Or try the VLOOKUP function instead, it's easier.

There's also Merge Sheets add-on (with a fully-functional 30-day trial period) that does everything for you without formulas.

Hello,

I've four column in sheet1 (Error1, Error2, Error3 & Score) and there are two column in sheet 2 ( Error Category & Weightage) If I select the errors in three columns in sheet1, in the score column, the score should be appear substracted by 100. can you please suggest function for it? Below is the table

Sheet1

Error1>>>Error2>>>Error3>>>Score

Font>>>Layout>>>alignment>>>(the score will be substracted by 100)

Sheet2

Error category>>>Weightage

Font>>>10

Layout>>>10

Alignment>>>5

Hello Rakesh,

For me to be able to help you better, please share a sample spreadsheet with us (support@apps4gs.com) with 2 sheets: (1) an example of your data (2) the result you expect to get (the result sheet is of great importance and often gives us a better understanding than any text description). I kindly ask you to shorten the tables to 10-20 rows.

Note. We keep that Google account for file sharing only and don't monitor its Inbox. Please do not email there. Once you share the file, just confirm by replying to this comment.I'll look into it.

Hi Natalia,

Thanks for the feedback.

I've shared a spreadsheet with you. Please let me know if you can access the sheet.

Thank you.

Hi Rakesh,

Sorry, I don't see any spreadsheets. To grant us access to it, press the

Sharebutton at the upper right corner of Google Sheets and enter support@apps4gs.com.Hi Natalia,

Sorry for the confusion.

Please click on the below link.

https://docs.google.com/spreadsheets/d/16HjbQM02Me7F2Grj8m1B_b2HGbCJg8Z3PaN5yXoB9Ow/edit?usp=sharing

Hi Rakesh,

I've got the file, thank you. I entered the formula into column E, please take a look.

Hi Natalia,

Thank you so much for creating the formula for my sheet. It's really helpful for me.

Many thanks,

Rakesh

You're most welcome, Rakesh!

Hello!

If i want a cell in sheet1 to reflect the last entry in column D in sheet2, what should i do?

Hello Jian,

Use this formula:

=INDEX(Sheet2!D:D,MAX(MATCH("zzz",Sheet2!D:D),MATCH(143^143,Sheet2!D:D)))

Hi Natalia,

Thanks for the great document, my issue is that I have more than one match and I want to find all of them and add their numbers from different column. But this formula only finds and returns the first match:

=INDEX(A2:M92, MATCH("search_key", G2:G92,0), 2)

Hi Bardia,

Our Multiple VLOOKUP Matches creates formulas that will solve your task.

You are just convincing me more and more that spreadsheets are becoming less and less relevant. It's like trying to use a hammer when you need a screwdriver. Wrong tool ... wrong place.

I have two spreadsheets

First spreadsheet has 4 columns:

Roll No., Name, Fruit, Quantity

1111 AAA Apple 3

2222 BBB Orange 1

Second Spreadsheet has COLUMN HEADING AS:

Roll No. APPLE ORANGE MANGO (Fruit) in column heading

Now I want formula in second spreadsheet in B2 that if data VALUE OF cell A2 and B1 (i.e. column heading) matches in First Spreadsheet then put the value of D2 in B2 (relative formula is required)

Hello Ranjeent,

You need a formula like this:

=IFERROR(ArrayFormula(INDEX(Sheet1!$A$1:$D$10,MATCH(CONCATENATE($B2,C$1),Sheet1!$A$1:$A$10&Sheet1!$C$1:$C$10,0),4)),"")

Please look through this part of the blog post to understand how it works.

Hi,

I'm trying to pull one row of data by name from a master google excel document with 5 sheets of data (List of Names/Data) into another sheet. Is there a way that I can use Query and Importrange to list all five sheets and use Match to find each name into the new sheet?

Hi N,

Please specify if you work in Google Sheets or Excel (you mentioned both so it's not entirely clear).

how can I import a range from a different gsheet into the INDEX formula

i tried =index(importrange("URL","Sheet1!B2:G1285"),MATCH(G3,B2:B1285,0))

and i got this error: Circular dependency detected. To resolve with iterative calculation, see File > Spreadsheet Settings.

Hello Melissa,

The 'circular dependency' error means that your formula refers to a range that contains the formula itself. Just put it in another cell outside the range you refer to or change the ranges inside the formula.

I made a sheet with 220 sheets(tabs), i used " iferrrorarrayvlookup" 300 times, and two master sheets(all tabs data at once by formul eg: "{a!A1:Z75;.......}), And i used one now formula, which is linked to all sheets,

present problem is: its taking load for 2 min when i open in android or pc what is the reason of it ?

Hello Shravan,

There are lots of things that can impact Google Sheets speed. But I believe the answer is in your question :) It takes time for Google Sheets to load the data returned by the array formula that refers to other hundreds of formulas that, in their turn, reference hundreds of sheets. Try splitting the data into separate spreadsheets and/or reference fewer data in formulas.

Hi, Thank you for the very thorough instructions. I am wondering how the below formula can be converted to use the match function to replace the Vlookup Index number. I have tried but I can't seem to get the correct result.

Current working formula =SUMIF(VLOOKUP(A20,'Sales Comp GBP'!$F$2:$CC$1000,48,false),"#N/A")

Tried- =SUMIF(VLOOKUP(A20,'Sales Comp GBP'!$F$2:$CC$1000,MATCH("MTD_net_pub_comp",'Sales Comp GBP'!1:1,0),false),"#N/A")

This formula doesn't seem to bring back the same result. do you know why this could be the case

Hi Rob,

I am a bit surprised your first SUMIF works since its VLOOKUP returns one value rather than an array, so there's nothing to sum.

Would it be possible for you to create a shortened version of your spreadsheet along with a formula and share it with us? It's support@apps4gs.com.

Note.We keep that Google account for file sharing only, please do not email there. Once you share the file, just confirm by replying here.Hello, firstly i'd like to say thank you for all the guides, they have really helped!

So I have one sheet with 2 tabs on it. First is called "Items" and second is called "Master".

Here is the simplest way to describe what I need:

Reference: Items!A:A (List of unique ids)

Look here for a match: Master!C:C (List of ids, some repeated)

Return: Master!B:B (info to go with the ids)

Into: Items!G:G (results need to be in one cell and separated by commas)

The tricky thing is there may be any number of matches in Master!C:C from 0 upwards, and I need all of them except the first one since that one is different and I already have that in Items!E:E. If there are no matches it stays blank.

I am currently using this formula to get the first match in Items!E2, but I don't know how to adapt it for the rest of the matches and exclude the first to go in Items!G2.

=IFERROR(INDEX('Master'!$B:$B, MATCH ($A2, 'Master'!$C:$C, 0)))

Hello!

Unfortunately, without seeing your data it hard to give you advice.

For me to be able to help you better, please describe your task in more detail. Please specify what you were trying to find, what formula you used and what problem or error occurred.

Give an example of the source data and the expected result.

It’ll help me understand it better and find a solution for you. Thank you.

Sorry about that, I couldn't come up with a better way to explain (it already took me an hour to write the question the first time). It also turns out I didn't need to cut off the first match, so that made it a lot simpler and I ended up using FILTER like this instead:

=IFERROR(CONCATENATE("{ ",JOIN(", ",UNIQUE(FILTER('Master'!$B:$B , 'Master'!$C:$C = $A2 ))," }")))

Unfortunately I still can't figure out how to sort the results by 'Master'!$A:$A but it's getting there...

Thank you for trying to help anyway.

Hello!

Correct your formula to write your search results on a single line.

=IFERROR(CONCATENATE("{ ",TEXTJOIN(", ",TRUE,UNIQUE(FILTER(Master!$B:$B, Master!$C:$C = $A2))," }")),"")

Thanks, I didn't know TEXTJOIN was a thing since I always get the same result, but I guess there wil be times when it matters so it's good to know.

Do you know how I can get the results in alphabetical order of Master!$A:A without having to sort that sheet manually?

I can only figure out how to use SORT to get the results in alphabetical order of themselves (Master!$B:B) which is no use to me.

Hello!

You have not used the link Master!$A:A. Please specify what you were trying to find.

Currently the results are in the same order as they appear on the Master tab but I cannot rely on people to remember to sort that tab alphabetically by column A manually after adding things to it.

Since I am taking results from Master column B instead of Master column A I can't figure out how to use SORT on the results without sorting them alphabetically according to column B which is not what I want. I need the results to be appear as if Master tab had been sorted alphabetically by Master column A before the formula was applied, even though it is not.

Nevermind I figured it out. Also TEXTJOIN didn't work but JOIN does. Thanks very much for your time and trying to help though! Sorry I cannot explain things clearly.

Apologies TEXTJOIN does work when I remembered to put TRUE in. Thanks!

I need to match with max of col index in google sheet. But couldnot able to acheive it

Eg. Col A has 10 prod codes (may repeat many rows)

Have to match with max of col B values respectively

Product Code Test %

A 50

B 55

A 70

In the above case if I filter A, then the restult should be MAX(50, 70)which is 70

Hello Saravana,

If I understand your task correctly, the MAXIFS function may help you. It returns the maximum value from one column based on the condition in another column. For you it may look like this:

=MAXIFS(B2:B10,A2:A10,"A")

If you mean something different, please describe your task in more detail.

great tutorial. thank you so much!

Hello, I hope you're fine.

I have an issue on Gsheet in a INDEX(MATCH formulae.

First, I get value (invoice's numbers) from differents sheets with a filter formulae.

Secondly, I have to make an INDEX(MATCH of these values with an extraction of my accounting software.

I don't know why, the return is "Did not find value '2020026' in MATCH evaluation." but, when I use ctrl + f, I find this value in my extraction.

For your information, my formulae is juste below :

=IFERROR(

IF(A2="";"";

IF(INDEX('COMPTA 2000150200'!$A:$A;MATCH(A2;'COMPTA 2000150200'!$A:$A;0);1)=A2;"OK";"NOK"))

;"NOK")

A2 was get by filter function. But when I replace "A2" by the invoice number (2020026), it's works !

Can you help me please ?

Thanks in advance,

Regards.

Hello Corentin,

Please double-check the format of the values returned by FILTER and of the value in A2 which is used in MATCH. If they differ, this can cause difficulties.

If this is not your case, please consider sharing an editable copy of your spreadsheet with us (support@apps4gs.com). I kindly ask you to shorten the tables to 10-20 rows.

Note.We keep that Google account for file sharing only, please do not email there. Once you share the file, just confirm by replying here.We'll look into it and see what may be causing difficulties.

How to return multiple search results using index match? The Aggregate function of excel does not work

Hello John,

Do you need to return multiple matches in rows or columns? What formula are you trying to use in Google Sheets exactly?

Hi I have 3 sheets

One name "Draw Results" Contains numbers e.g. 002345

(numbers are filled on the entire sheet e.g. 'Draw Results' A1:K159)

Second Named "Bond List" Contains numbers e.g. 002347

(numbers are filled on the entire sheet e.g. 'Bond List' A1:K159)

Third Named "Matched Results" want the matched results displayed in here.

What Formula do I put in here I have tried =INDEX('Draw Results'!$A$1:$A$159,MATCH('Bond List'!A1,'Bond List'!$A$1:$A$175,0),1)

Please help

Thank You

Hi Frais,

For me to be able to help you, please specify whether you're trying to compare two sheets for duplicates or find matching data between two columns in two sheets and pull the corresponding data from some other column.

Help! I have two sheets: MasterSheet and Operations Sheet.

I need to replicate Columns H and I on the MasterSheet has two columns (H and I) in the Operations Sheet in Column C (which corresponds to Column H on the MasterSheet) and Column D (which corresponds to Column I on the MasterSheet).

I need a formula where Column D will automatically populate with the cell value in Column I on the MasterSheet if the value in Column C matches the value in Column H of the MasterSheet. Essentially, if Column H and C are the exact match, then Column D should populate with the value in Column I in the same Row as Column H.

Hello Elsa,

I'm really sorry but your description is not clear.

For me to be able to suggest you anything, please try to specify (consider bulleted list) what columns each sheet contains exactly and what data should be pulled and where exactly. Thank you.

I have a Worksheet with 2 tabs.

The first tab is named MasterSheet.

- Column H heading on the MasterSheet is called "Purchase Req"

- Column H on the MasterSheet is called "Purchase Order"

The second tab is named OperationsSheet

- Column C heading on the OperationsSheet is called "Purchase Req"

- Column D heading on the OperationsSheet is called "Purchase Order"

I need a formula for Column D on the Operations Sheet that:

1) Searches column C on the Operations Sheet for an exact match as that in Column H of the MasterSheet

2) If it finds the exact match, Column D in the OperationsSheet will then return (or copy) the value found in Column I of the MasterSheet.

Example:

MasterSheet OperationsSheet

H I C D

PR PO# PR PO#

1234 450001234 6738 450089628

6738 450089628 1234 450001234

Thank you very much for the detailed explanation, Elsa!

Now I've got your task. Here are the formulas for you to try (assuming there are 10 rows in a table, so please adjust the number 10 to your number of rows):

=INDEX('Master Sheet'!I1:I10, MATCH(C2, 'Master Sheet'!H1:H10, 0))

=ARRAYFORMULA(VLOOKUP(C2:C10, 'Master Sheet'!H2:I10, 2))

Number 2 here is the number of the column I in my small table. Please also change it to yours.

Hope this helps!

Thank you, this is very helpful! One last question, how do you add to the first formula to replace the #N/A error with a blank cell if the searched value is not found?

You're most welcome, Elsa!

As for you question, whatever formula you choose, just wrap it in IFERROR as described here.

Hi Sir

Thanks for your very usefull tutorial.

I need your help in very small thing. I am converting a simple formula into array formula but its not working form me for some reason.

Normal formula is =IF(AND(A3<'IIR & NPV - Calculation Dashboard'!$B$2,OR(A3=1,A3=13,A3=25,A3=37,A3=49)),'IIR & NPV - Calculation Dashboard'!$B$12,0)

Its Working as expected for me. But when I am converting it into Array formula, it looks like this

=ArrayFormula(IF(AND(A3:A<'IIR & NPV - Calculation Dashboard'!$B$2,OR(A3=1,A3=13,A3=25,A3=37,A3=49)),'IIR & NPV - Calculation Dashboard'!$B$12,0))

It is not giving me the same result as its normal formula.

Can you please help me out here and tell me what is the error in this array formula?

Hello!

If I understand your task correctly, the following formula should work for you:

=ArrayFormula(IF(IF(A3:A20 < $B$2,TRUE,FALSE) * (IF(A3:A20=1,TRUE,FALSE)+ IF(A3:A20=13,TRUE,FALSE)+ IF(A3:A20=25,TRUE,FALSE)+ IF(A3:A20=37,TRUE,FALSE)+ IF(A3:A20=49,TRUE,FALSE)),$B$12,0))

Please adjust this formula according to your data if needed.