Because it is an array formula you need to enter it with CTRL+SHIFT+ENTER.

We can then copy the formula down to cells E6 and E7, which is as many as we need since there are a maximum of 3 results for any one person in the list.

When we copy the formula down the ROW(A1) reference will update to ROW(A2) and so on, and as a result it will return the 2nd, then the 3rd result….more on that later.

Evaluating the Formula

Essentially we’re using an INDEX function to lookup the name in cell E4 in the range A5:B11 and return the values in column B that correspond to Bob.

Remember, the INDEX Function returns a value at the intersection of a particular row and column in a given range.

The syntax for the INDEX function is:

=INDEX(array,row_num,[column_num])

In this formula we’re employing the help of SMALL, IF and ROW to complete the row_num argument.

Step 1 – IF Function

The IF Function checks to see which values in cells A5:A11 = Bob, and then returns the row numbers that match.

Rows 2, 5 and 7 contain the name Bob (that is the row numbers in the range A5:A11, not the worksheet row number. Hence the minus 4 in ROW($A$5:$A$11)-4) which accounts for rows 1-4 that aren't part of our range.)... so, our formula now looks like this:

=IFERROR(INDEX($A$5:$B$11,SMALL({2,5,7},ROW(A1)),2),"")

Step 2 – SMALL Function

The SMALL Function returns the k-th smallest value in a data set. The syntax is:

=SMALL(array,k)

Where k is the position in the array to return.

And, since the ROW function returns the row number of a reference, ROW(A1) evaluates to 1, therefore our SMALL formula is:

SMALL({2,5,7},1)

And evaluates to 2, i.e. the smallest value in the array.

Now our formula looks like this:

=IFERROR(INDEX($A$5:$B$11,2,2),"")

And in English it reads:

Lookup the range A5:B11, find the value at the intersection of the 2nd row and 2nd column, if there is an error; return nothing.

Remember when we copy down the formula to cells E6 and E7 the ROW argument changes to ROW(A2) and ROW(A3) respectively. As a result our SMALL formula evaluates as follows:

Cell E6: SMALL({2,5,7},2)

And:

Cell E7: SMALL({2,5,7},3)

Step 3 – INDEX Function

If we didn't use IFERROR and we selected a name that only has 1 or 2 results we would get an ugly #NUM! error. Instead, with IFERROR we can instruct Excel to leave the cell blank. I think you'll agree cells E6 and E7 below look nicer blank:

Alternatively you could replace blank with N/A or similar if you prefer.

Enter your email address below to download the sample workbook.

By submitting your email address you agree that we can email you our Excel newsletter.

Please enter a valid email address.

Download the Excel Workbook. Note: This is a .xlsx file. Make sure your browser doesn't change the file extension to .zip or you will end up with gobledy goop (technical term for utter nonsense).

Thanks to Bob for suggesting this tip.

Bob has worked in heavy engineering in the steel industry for over 40 years.

"For the later part of my career I have been using AutoCAD for design. I have for the past three to four years been using excel for minor “filing” purposes. I have in the past 8 months started to explore excel and have realised that there is a very powerful programme not being used so I am studying as much as I can."

Vote for Bob

If you’d like to vote for Bob’s tip (in X-factor voting style) use the buttons below to Like this on Facebook, Tweet about it on Twitter, +1 it on Google, Share it on LinkedIn, or leave a comment to thank Bob for taking the time to suggest this tip….or all of the above 🙂

Hi Daniel,
You should setup a connection to that file, to bring data in. Usually, power query is the tool for this. More, if you use power query, you might not need formulas any more, the lookup can be performed within PQ.
Catalin

I was wondering if there was a way to lookup one search field using this function, format, from a different spreadsheet within the book, but when it finds what was called, it copies all corresponding data from that row in where the code syntax is underneath it

You can’t do that, if you want to return results from multiple columns, same row, you have to set formulas in each cell, each should return values from the corresponding column.
If you open a new topic on our Forum with a sample file and details of what you need to do, we will help you find a way to do that.
Catalin

Hi, I need help on my formula. I need multiple number’s from one column on sheet 1 to be returned to a row in sheet 2. What I can’t get right is to transfer the cells that meet the criteria in merged cells in sheet 2 from A1:H1. I basically need the information to be transferred starting from I1. For some reason when I change the formula column part to 5, the cells get repeated two or three times. Here is the formula I’m using;

Hi Vinette,
Can you please upload a sample file with your formula on our forum? (create a new topic). It’s not easy to understand what’s wrong without seeing the data. Why are you using COLUMN()/5 in the second argument of the SMALL function? That argument expects integers, not fractional units.
Catalin

I have question:
I want to match company names list with employees list for more than one employee name
I tried Vlookup and it gives me only one employee name per company.
=Vlookup(B1 company name, A:C range, 2, False)

Also, I used Index, match:
=index(A:A range of emloyees,Match(B1 company name, C:C range of company names in the other excel, 0))
But it didn’t work either and it gives me the same result (only one employee per company)

I need small help. So I need to get how many same numbers repeats in Column A that matches same value in column B. Example How many times 38692 repeats in column A and has value of $5 in column B? Can anyone help me with that please?
A B
38770 $10.00
38692 $5.00
38692 $5.00
38692 $5.00
38692 $10.00
38769 $10.00
38692 $5.00
38692 $5.00
38692 $10.00
$0.00
$0.00
38694 $10.00
$0.00
38694 $10.00
$0.00

I believe I understand everything except, how do highlight the Name/Score row upon name selection with the drop down list? I believe I could use a macro or VBA but I can’t figure out how you’re doing it.

I have lowest price from many offers that quoted from different suppliers. Now I want to pick up the name of supplier that offered lowest offer and second lowest offer and third offer with supplier name from the worksheet. Can you assist in this? pls help

Hi Leandro,
You have to adjust the ranges in formula, they are not dynamic, they are referring strictly to $A$5:$B$11 range. If you need more help, you can open a new ticket on Help Desk, with your test file uploaded.
Cheers,
Catalin

Hi ..thanks for the wonderful solution .. however, there is a big limitation to it as we have to know beforehand how many matches are we going to see ..is there anyway it can automatically detect the number of matches and populate them accordingly ..

I really enjoy your Excel articles that are very technical and useful.
I have a particular problem below and would like to ask for your help.

Instead of looking-up and returning multiple matches for one single entry, I would like if it can do for multiple entries given from an input list on one column or separate worksheet.

To output the results, each row will show the matching entry and its matching values can be listed on the next column and/or next column as many as the matching values are found.
And it keeps posting on the next matching entry in the second row and so on for the next.. until all given entries in the list are exhaustedly looked up.

I’m trying to use this formula to automatically sort mice from one list by cages. I can get it to work for the first cage table (cage 1), but when I try and recreate the formula for another cage table (cage 2) I get nothing (error if I remove the IFERROR condition). If i try and break the formula down, the IF value will only generate a result if the reference cell reads 1, however the original cage table works with any value (so long as it’s in the mouse table). The formula seems to break down completely once I get to the SMALL segment of the formula (if it makes sense to check it that way)…

Hi Maria,
In this formula:=IFERROR(INDEX($A$5:$B$11,SMALL(IF($A$5:$A$11=$E$4,ROW($A$5:$A$11)-4),ROW(A1)),2),"")
The data table has 2 columns:$A$5:$B$11, and the data is returned from column 2 (this is the value for the column argument of INDEX function).
You can have a data table with any number of columns you want, just make sure that the data to be matched is in first column, and change the column argument for INDEX function to your desired column number.
Catalin

I have a list of states,city,assessment rates in excel .. The solution i want for this is , in a dropdown box if i select the state , it should load the cities of that particular state and then when i select the state it should give the max assessment rate of that city by comparing for ex:

State city assessment rate

New York New York west 38.23
New York New York east 28.20
New York New York north 20.36
New York New York south 42..23
New York New York 35.55

If i enter new york in dropdown box it should give the max value by comparing all values

Thanks

sorry their’s a mistake in above line.. if select state it should load cities of that particular state and then if i select city it should display the max value of that particular city

Hi Shaan,
Please upload a sample file with details to our Help Desk System, it’s easier for us to work on a file instead of a description.
If you prefer a general solution, then the solution is to use OFFSET in a defined name to create the range for that city only, for the second dropdown.
Catalin

Column B in this formula must be Supplier’s Names column, column D must be the list of prices.
Note that if multiple suppliers have the same minimum price, the formula will return only the first supplier with the minimum price.
Catalin Bombea

I love this tutorial, thank you so much!
I still have an issue and I hope you can help, possible with a VBA code.
How do I insert new row for every match found? In your example you created several rows below for it. Can new rows be inserted with the multiple values?

This article is very helpful. One question, though – I need to be able to sort the data that is returned by the formula (in your example, the results in column E). Is there a way to modify the formula to sort as well?

Hi Betania, the formula for sorting is quite different and complex. The most easiest way to have the results sorted in any way you want: ascending, descending, is to select the data table A4:B11, then sort it by Score, smallest to largest, or largest to smallest; you’ll see that the results will be sorted. If this method is not satisfying, let me know; i’ll work anyway on a solution like that, maybe it will be useful for our members.
Catalin

Unfortunately, the formula is pointing to a pivot table and with the way it’s set up, I am not able to sort it for the columns I am using. I tried different methods of combining the above formula and Oscar’s formula here: http://www.get-digital-help.com/2009/03/27/sorting-text-cells-using-array-formula/, but I could never seem to wrap my head around how it should be put together in order to work. If you can help, I would appreciate it. If not I’ll try to find some other workaround.
Thanks!

Hi Raghuram,
Let’s take this formula piece by piece:
1.IF($A$5,ROW($B$11),ROW($K$11))
The formula is checking cell A5, if this is TRUE (1) , returns ROW($B$11), if it is FALSE or (0), it will return ROW($K$11);
this is a nonsense, because, no matter if A5 is TRUE (1) or FALSE (0), the result of this formula will be 11, ROW($B$11) will result 11, same as ROW($K$11). Further calculations, which deducts from the previous result the number of the current row: “-ROW())*10” has no meaning to me, i have no idea for what is designed this formula;
2.ABS(IF($A$5,0,COLUMN($K$11))-COLUMN())
Depending if cell A5 is true or false, it will return 0 or the difference between column K number (which is 11) and the current column number (where the formula is), in absolute value;
3. IF($A$5,COLUMN($B$11),0)+1
This part of the formula will return the column B number (which is 2) if A5 is true, or 0 if A5 is false

the result from step 1, added to the result of the formula from step 2, minus the result from step 3, will be compared to cell M3 value, the result of the entire formula will be TRUE or False…
Hope it’s clear enough,
Catalin

Hi Robert,
Can you upload a workbook via the help desk with a sample of your data, just to see how it is structured? It’s a lot easier to work with your data structure, i’m sure you will understand that.
Thank you, Catalin

I have cell A1 with a data validation drop-down list containing 7 topics. Each of those topics has numerous articles. I’m trying to get cell A2 to display another list (from range of cells) of those articles for the specified topic only. Any suggestions?

Hi there! Great tutorial, probably one of the best formats I’ve seen to date with the step-by-step understanding breakdown to help us mortals get the big picture and be able to modify to suit our own needs.

Advice?: I’m running a similar formula down 1000 rows and 2 columns to pull back matched data from another sheet that contains some 50,000 rows x 60 columns. The sheet then runs some basic statistics against the returned data and calculates averages, creates a nice bell curve, that kind of thing. The problem is that this method is extremely processing heavy. It takes my relatively new computer about 30-40 seconds just to iterate whenever I make a change. Is there anything I can do to significantly improve the processing efficiency in terms of modifying the formula for the large data set? Thanks for any assistance!

I have used this formula similarly to the way it is used in this tutorial. However, I find that if a row is deleted or if the crieria column contains a blank value in the midst of other non-blank cells, it trips the formual up. Can you provide some insight on that, please?

I don’t have any problems with blanks or deleting rows in my source data effecting this formula. Have you downloaded the workbook and tested it? Perhaps there is a slight difference between this formula and yours?

If you can’t figure it out feel free to send me your workbook via the help desk and I’ll take a look. If you send it please explain exactly what you’re doing to break the formula.

Hi,
This formula is great. How would I change the formula if the data table was the opposite way? so the name was in column B and the score in column A but the score was a name and not a number?
I am trying to adapt this to create a team structure for a spreadsheet I have. In my table I have the employee names in column A and Manager in column B but want the exact same result you display here.
Many thanks

Thank you Bob! Saw the same approach on another website but it was so complicated, my perseverance paid off when I hit upon this site. Am a budding excel enthusiast and you just made my day. Bless You !

I have attempted to replicate this solution without success using Excel 2003. Should I assume this solution would function properly on 2003? If not, are there any modifications to the formula you would suggest?

The IFERROR function was new in Excel 2007 so that part of the formula won’t work. You could remove the IFERROR and the rest should work but if the value isn’t found it will display the error. You could use the IF(ISNA( soltuion as a workaround in Excel 2003.

Hello Bob,
thank you – to simple to express gratitude.
Intelligent, clear, understandable, nice, neat, precise, valuable = superlative.
Great feeling that there are still people with Excel’lent brains, who have the need to to something good for other people.
Best wishes.
(By the way “Excel the Great” is totally underestimated – big, big waste).

I’m just beginning to learn about excel that is why i keep downloading your free tutorial. This summer i’ll try to study them all and if i can make it, i would try to make our school records computerized(? or digitalized?). I’m hearing impaired so i rely learning more through reading and I’m glad you are sharing your expertise here for free. Again, thanks a lot and more power.

This is great, thanks.
Is there a way that I can format the formula so it would pull across multiple columns rather than multiple rows? i.e. the results here would display in e5, f5, g5 rather than e5, e6, e7.

I know I’ve done this before and for the life of me can’t remember how.

Duncan, thank you for that question as well. I’m sure I’ll run across it soon enough. 😉

Carlo,

Perhaps, I’m not understanding exactly what code to change if I wanted to have the formula evaluate a date (preferably in a “mm/dd” format). I’ve tried just about everything I can think of and still nothing.

On that note, I would assume that once I get the date feature working, I could have it check against another column of dates as well. I’m tracking employees and each one has a certain code that may happen at certain dates so it’s imperative that I exclude them after I have inputted a date.

This tutorial has been a MAJOR help to me and the work I do for my organization. I cannot say “thank you” enough in how this will increase my productivity. I do have one question however. Is it possible to exclude data if say a 3rd column is marked (i.e. Column D = “x”). I would prefer to not have the data populated even if it matches the initial criteria. Many thanks to your response.

We need VBA for this.
The reason is simple. The key to this example — the workbook in this post–
is the SMALL function. And we cannot go inside it and put more conditions
as to what it will pick because it is ‘canned’. Even if the right IF CONDITIONS are set with , i.e.,
the x’s and the names, these are all external to the function SMALL which will
be the one responsible for picking up the values.

Function BaRRay(LkUpValue As String, tbl_array As Range, find_col As Integer, ret_col As Integer, arr_show As Long) As Variant
Dim r As Long
Dim i As Long
Dim arr
Dim str As String
ReDim arr(1 To 100)
For r = 1 To tbl_array.Rows.Count
str = tbl_array.Cells(r, find_col).Offset(0, 2).Value
If UCase(tbl_array.Cells(r, find_col).Offset(0, 2).Value) <> "X" And _
LkUpValue = tbl_array.Cells(r, find_col).Value Then
i = i + 1
arr(i) = tbl_array.Cells(r, ret_col)
Else
End If
Next
If arr(arr_show) = "" Or IsError(arr(arr_show)) = True Then
BaRRay = ""
Else
BaRRay = arr(arr_show)
End If
End Function

IMPLEMENTATION: Simply use this one like an ordinary Excel Function.
1) Copy the whole sheet to Sheet2.
2) Copy this formula to the first Result Cell in the example.
That would be at E5.

=Barray(E$4,A$5:C$11,1,2,ROW(A1))

3) Get the handle of E5’s cell and drag the formula down as needed.
Note: This is an array function but it doesn’t need CTRL+SHIFT+ENTER
as it is customized in the code. The value used to display
the array’s items is through the ROW function’s values.
4)There are around 100 rows in the array built in , in the code. You can
change it in the module if you like by changing the part “100”:
ReDim arr(1 To 100)

Awesome! I was slightly bummed to see that I would have to use VBA (admittedly I tend to shy away from it as I honestly haven’t learned it), but seeing how it works really helped me. I’m sure this will open the door to many more things I can conjure up.

Would I be correct in assuming that if I wanted it to evaluate ANY date rather than an “X”, I would need to add the following in somehow:

Honestly, maybe there are some out there that will prove me wrong.
But I have been asked similar out of this world look-ups and most of
the time Excel Functions would come out short. Hence, I don’t want
to waste so much time thinking about the solution in purely excel terms.

Anyway,I don’t know why you would want to treat a date as a string.
But it’s okay as long as you would type in your criteria in the sheet
to match the code’s criteria.

Or you may declare it as a date instead.
Dim LValue As Date
LValue = format(Date, “yyyy/mm/dd”)

Above all, you can’t do look-ups on multiple sheets.
First, Index function won’t work with a 3D named range.
Second, Using Indirect Function for multiple sheets’ reference
will work only with a SUMPRODUCT as far as I know and
I don’t think you need a sumproduct.
Conclusion: VBA to the rescue.
This is just a copy and paste routine.
1) ALT + F11 (this will bring you the VBE Window)
2) While in the VBE Window, Click Insert Menu , Add Module (note: NOT class Module)
3) Double Click the Module, and copy and paste this code:

Function SagitMultipleSelect(straddress As String, strcriteria As String, col_return As Integer, arr_show As Integer)
Dim ws As Worksheet
Dim wb As Workbook
Dim arr
Dim r As Long
Dim i As Long
ReDim arr(1 To 100, 1 To 1)
Set wb = ActiveWorkbook
Dim wr As Range
For Each ws In wb.Worksheets
If ws.Name = ActiveSheet.Name Then
Else
Set wr = ws.Range(straddress)
For i = 1 To wr.Rows.Count
If wr.Cells(i, 1).Value = strcriteria Then
r = r + 1
arr(r, 1) = wr.Cells(i, col_return).Value
Else
End If
Next
End If
Next
If arr(arr_show, 1) = 0 Then
SagitMultipleSelect = ""
Else
SagitMultipleSelect = arr(arr_show, 1)
End If
End Function

IMPLEMENTATION: Just use this like an ordinary Excel Function. This is an array actually, but you don’t need to CTRL+SHIFT+ENTER.
Just make sure the first formula is okay and drag it down.

Data: All Sheets in the file except the Activesheet where the formula is and their range R1:T6.

=SagitMultipleSelect("R1:T6","sagit",2,ROW(A1))

strADDRESS- This is simply the table_array you’re going to look-up.
strCriteria – The criteria for looking-up (similar to Vlookup).
col_return – The column in your table to be shown and stored in an array.
array_display – The row number of the array in which the stored data is found and displayed in the formula.
The first formula should always be ROW(A1). So when you drag it down it will all return the values/rows
within the array.

I am trying to create a formular which will look up Supplier Names on one spreadsheet and tell me if they appear or not on another spreadsheet (this way i will know if i have missed any suppliers of my list or infact added some which should not be there)

The formula is simple. The first argument is the lookup value in A2.
The table to check or lookup is in Sheet2 A2:A3. The column to return is
column 1 because there’s only one column
(see Sheet2 illustration below). FALSE simply means exact match.
Note that the Lookup value (i.e. A2 and A3 above) may not necessarily
have absolute references. Only the Table_Array which is in Sheet2:A2:A3.

any one can help me out, i am maintaining daily sale with dates, below is rate detail, through vlookup function i am getting rate, but from 1st Feb rate has been changed, i want previous rate should be at 10 but after 1st Feb vlookup pick rate 30.
Date Item Rate
5-Jan-15 Bolt 10
01-Feb-15 Bolt 30

Hi Waseem,
Please prepare a sample workbook with your problem, no one can guess why you get this result; you may have duplicates, and the formula returns only the first match, or can be other problems in your data. Use our Help Desk system.
Catalin

I have been asked to do a similar task ; Unfortunately, I still have not discovered the function to replace the SMALL function
or any function that would simply list items especially non-numeric ones into an array.

So my solution to you here is an instant VBA code I made just for you.

Function ListCode(YourNamedRange As Variant, YourCriteria As String, ret_col As Long) As String
Dim r As Long
Dim v
Dim i As Long
ReDim v(1 To YourNamedRange.Rows.Count)
For r = 1 To YourNamedRange.Rows.Count
If YourNamedRange.Cells(r, 1).Value Like "*" & YourCriteria & "*" Then
i = i + 1
v(i) = YourNamedRange.Cells(r, 1).Value
Else
End If
Next
If IsError(v(ret_col)) = True Then
ListCode = ""
Else
ListCode = v(ret_col)
End If
End Function

Use this function like any other. It is an array; however, it doesn’t behave
like any other array formulas in excel;that is, no need for CTRL+SHIFT+ENTER.
So just drag it down. Also the list got to start with the ROW(A1) function to signify
1. I think you already know that but I am saying it anyway.

=ListCode(code,"TG13",ROW(A1))

Please note that you don’t need to put a wildcard character (*) of your criteria, it is
hardcoded in the vba code above.

That is why we call this instant code. 🙂
My apologies. Please replace your existing code
with this one in the Module.

Function ListCode(YourNamedRange As Variant, YourCriteria As String, ret_col As Long) As String
Dim r As Long
Dim v
Dim i As Long
If YourCriteria = "" Then Exit Function
ReDim v(1 To YourNamedRange.Rows.Count)
For r = 1 To YourNamedRange.Rows.Count
If YourNamedRange.Cells(r, 1).Value Like "*" & YourCriteria & "*" Then
i = i + 1
v(i) = YourNamedRange.Cells(r, 1).Value
Else
End If
Next
If IsError(v(ret_col)) = True Then
ListCode = ""
Else
ListCode = v(ret_col)
End If
End Function

the difference is this line of code:”If YourCriteria = “” Then Exit Function”
It exits if the criteria is blank.

1) ALT + F11 (This will bring you to the VBE Window)
2) While in the VBE Window, INSERT Module (Note: Not Class Module)
3) Paste this code:

Function ChrisArray(dtLkUp As Date, shftLkup As String, TBLArray As Range,find_1 as Integer, find_2 as Integer, Col_Return As Integer, RN As Long) As String
Dim r As Long
Dim v
ReDim v(1 To TBLArray.Rows.Count)
For r = 1 To TBLArray.Rows.Count
If TBLArray.Cells(r, find_1).Value = dtLkUp And TBLArray.Cells(r, find_2).Value = shftLkup Then
i = i + 1
v(i) = TBLArray.Cells(r, Col_Return).Value
Else
End If
Next
If IsError(v(RN)) = True Then
ChrisArray = ""
Else
ChrisArray = v(RN)
End If
End Function

In your column of formulas, do this:

=chrisarray($BL$2,$BL$3,$B$10:$D$18,2,3,1,ROW(A1))

Assumptions:

BL2 is Date Lookup (changeable)(Always Absolute Reference with dollar signs)
BL3 is Shift Lookup (ie Day or NIght)(changeable) (Always Absolute Reference with dollar signs)
B10:D18 the table(you may expand this)(Always Absolute Reference with dollar signs)
2 – is the column in your table where you want to find your date lookup (changeable)
3 – is the column in your table where you want to find your shift lookup (changeable)
ROW(A1) – it will return the part of or give the effect of an array i.e. row 1 to 2.
Note: make sure ROW(A1) will be the first variable in your first formula.

Note that this is a customized array formula. You don’t need to CTRL+SHIFT+ENTER
Just enter the first formula correctly, get the handle and drag down the formula.
You may need to redo this all the time if you have added new entries beyond the Table Array
of your formula, but if you are just editing entries in the table then no need.

Sincerely,

CarloE

PS: If you’re new to VBA, you may encounter security warnings. Just agree to anything. Also, make sure
VBA is on by going to Excel Options, Trust Center, Trust Center Settings, Enable Macro and ActiveX.

Thankyou for getting back to me so soon i appreciate it very much, i am not to familar with the VBA function at all, do i copy and paste that code as is or do i need to enter information to relevant to what i am try to achieve, i.e do i need to enter my ranges and what not?

is there anyway i can conac you or send you my spreadsheet so you have a better understanding of what im trying to achieve?

Dear MYNDA,
I have a question regarding the drop down in $E$4 in this example. After selecting from $E$4, the background of name matched cells in column A will change to same background as $E$4, could you explain that a little bit? Where is the work for that part?

We both know that SMALL ignores text.
However, In this example – this post –
In its totality; that is, combined with INDEX etc.
SMALL can still return the values of column B even if you will
change the numbers to non-numeric.
or
you can play with the formula by replacing the argument col_num for INDEX FUNCTION from 2 to 1(in bold for emphasis)

Hi – so when I open the sheet and use it, it works. If I click in the sell such that it shows me the cells associated with the formulas, it then fails to calculate accurately. I have replicated this formula for my own needs, and get wrong answers… mainly because Small(False,1) = 0 instead of #NUM! so a value.

I think I know why it will fail after you’ve touched it… I mean clicked the cell as you said is because
they are array formulas. What you need to do is just place the cursor again in the formula and press CTRL+SHIFT+ENTER.
This will return its array functionality. Note: you can place the cursor in the formula anywhere you want as long as you won’t distort it.

I think it is the same with your “Small” function problem. Just press CTRL+SHIFT+ENTER. It will correctly show you
that ‘SMALL’ part as isolated. It will show either a 6 or a 10.

Thanks for your question. I’m not sure how your lookup table is structured so it’s a bit difficult to give you an answer. There is a tutorial here on looking up multiple criteria that might be of assistance.

Love the explanations on this site. Finally found a site where these are explained in plain English and not referring me to VBA (of which I’m totally clueless). I believe this formula will help me; however, I do have a question about how I put in the information to index from a different document to this one. I have a master list of candidates which I have sorted into lists by state and then further by city (where offices are located). Currently have been doing this by cut/paste (I know, groan). Just trying to find a way to automate this so when I pull reports to add info to the master list, I can then just go to each of the state and city lists and quickly update them. Can you help or direct me to someone who can?

I’m glad you found our site helpful. We appreciate it. 🙂
You know what, using a different document to Index is just the same as Indexing within the sheet. The only difference is that you should have both files opened when creating the actual formula. I’d like to take a look at your documents for me to be able to provide a better solution to your concern. I might even be able to come up with an easier one. Please go ahead and send us an email via the helpdesk and attach your file there along with a detailed explanation on what you want the formula to do. I’ll be more than happy to assist you with it.

Thank you so much for taking the time to write this up and explain how each step is working. It makes the difference between hacking things together from examples and actually “understanding” what is going on underneath, behind each command. You have saved me a lot of time and and manual work should I not have been able to get this to work.

Bob, thanks so much for sharing your experience with people around the globe and thanks for your excel tips which are changing the way we use excel and quicken our usage and save a lot of time.
Best wishes and continue with the excellent manipulation of data.

Resources

Affiliate Program

Microsoft and the Microsoft Office logo are trademarks or registered trademarks of Microsoft Corporation in the United States and/or other countries. Product names, logos, brands, and other trademarks featured or referred to within this website are the property of their respective trademark holders.

✕

Hang On, Don't Go Just Yet.

As a thank you for visiting how would you like a10% Discount Code to use with any of my courses?