Situation
There is no argument that VLOOKUP is a beautiful & useful formula. But it suffers from one nagging limitation. It cannot go left.
Let me explain, Imagine you have data like below. Now, if you want to find-out who made $2,133 in sales, there is no way VLOOKUP can come to rescue. This is because, once you search a list using VLOOKUP, you can only return corresponding items from the column at right, not at left.
Data:
One easy fix would be move the sales data to the left of person name. But this is an annoying fix, because, god knows you may want to lookup based on profit values or something else in future. A better alternative is,…
Solution
.., to use a formula combination called INDEX + MATCH (or OFFSET + MATCH would work too).
The basic syntax of this combination is like this: =INDEX(column with data you want,MATCH(value you are looking for, column which contains this data,0)). So, for eg: =INDEX($B$5:$B$17,MATCH(1088,$D$5:$D$17,0)) would find the position of 1088 in list D5:D17 and return corresponding element from B5:B17 (ie the value from left). See more examples below.
Examples:
Sample File
Download Example File – Make VLOOKUP go Left
Go ahead and download the file. It also has some homework for you to practice this formula trick.
Special Thanks to
Prem, Rohit1409, John, Godzilla, Chris Byham, judgepax – Please click on their names to learn even more.
Similar Tips
169 Responses
A good example of how easy, when you know index + match, how how to lookup anything anywhere.
Homework:-
1. =INDEX($B$5:$B$17,MATCH(LARGE($D$5:$D$17,2),$D$5:$D$17,0),1,1)
2. =INDEX($F$5:$F$17,MATCH(LARGE($D$5:$D$17,2),$D$5:$D$17,0),1,1)
3. =(LARGE($D$5:$D$17,1)/INDEX($C$5:$C$17,MATCH(LARGE($D$5:$D$17,1),$D$5:$D$17,0),1,1)) – (SMALL($D$5:$D$17,1)/INDEX($C$5:$C$17,MATCH(SMALL($D$5:$D$17,1),$D$5:$D$17,0),1,1))
Dear I want left site value by vlookup,
please share me,
Thankx
satendra kumar
NAME NO. NO. NAME
Amit 100609 100602
sumit 100602
ankit 100604
ravi 100605
shyam 100607
gaurav 100606
vijay 100603
=VLOOKUP(H2,CHOOSE({1,2},$C$2:$C$8,$B$2:$B$8),2,0)
Put this formula below NAME Column, it will work….
If there are same values in the Index-Match-Large values and you use the Largest “n” values, it tends to stop in the first same values it finds. How can one make it select the any of the next similar largest “n” value..???
I would like to perfom something similiar. What I am trying to do is extract number of pieces per person, the row goes from left to right and as data is entered I want it to populate and keep track of a rolling total for each month. Can you help?
Hello Jason,
One question. What if in this case ( if you want to find-out who made $1088) there is another person besides John? Does this formula only work for unique characters?
Thank you!
Hi Jason,
For Homework question 2: I have also used the same formula but I am getting value “Zero” as an answer. I think I am doing something wrong, Please tell me how can I correct.
=INDEX($F$5:$F$17,MATCH(LARGE($D$5:$D$17,2),$D$5:$D$17,0),1,1)
For Question 3: What if we use below formula and what is the difference:
=MAX(D5:D17)/MAX(C5:C17)-MIN(D5:D17)/MIN(C5:C17)
Please answer my query for clarity.
Sir, for example #1 you made, what if there are more than 1 person who has sales of 1088? What will be the result?
I think that, in this case, formula will show the first one that is in the column and have 1088 value.
1. =INDEX($B$5:$B$17,MATCH(LARGE(D5:D17,2),$D$5:$D$17,0))
2. =INDEX($B$5:$B$17,MATCH(MAX(D5:D17),$D$5:$D$17,0))
Chandoo…good examples in your workbook download. Worth noting that we can use full column references in this case, which cuts down on some of the characters:
1. Which person made sales = 1088
=INDEX(B:B,MATCH(1088,D:D,0))
2. Who made maximum sales?
=INDEX(B:B,MATCH(MAX(D:D),D:D))
3. Who sold to minimum number of customers?
=INDEX(B:B,MATCH(MIN(C:C),C:C,0))
4. What is sale per customer for the person who has lowest profit ratio?
=INDEX(D:D,MATCH(MIN(F:F),F:F,0))/INDEX(C:C,MATCH(MIN(F:F),F:F,0))
…which is really the same as giving them named ranges. And if you give them explicit named ranges, then the 4th one can be done like this:
=SUMPRODUCT((MIN(Profit_Ration)=Profit_Ration)*Net_Sales/No._Customers)
Hello Jeff….So EXCELLENT! Ur formulas say it all…& with CLARITY.
But i have a Q.
Assuming i have a data set of 100 sales reps & i want to know-
Who made among the top 10 for Sales??
ThanX
use conditional formating is easiest way to find top ranker and looser
Hi Chandoo, Just learnt a new trick (daddylonglegs on Excel Forum Site) to use Vlookup to look up columns to the left and thought you would be interested as have not seen it on your site. You can use Choose() to look up columns to the left i.e. in you example 1 above:
=VLOOKUP(1088,CHOOSE({2,1},$B$5:$B$17,$D$5:$D$17),2,0) = “John”
I am sure this has a great deal of other applications
regards, Mike
hi Mike, thnx for formula.
but can you please explain this logic of this formula, similar to explained by chandoo once in simple english.
thnx in anticipation
regrds,
saransh
Could you pl explai the choose function
CHOOSE({2,1},$B$5:$B$17,$D$5:$D$17
Hi Mike,
I have tried your formula
Its working and I am really happy…
thks a lot..
Please send me formula as given below:
Dear Chandoo, i want to match particular name ( Sheet 1 ) to name ( sheet 2) and after matching it has paste next column value( Sheet 2 ) to next column of name ( 1 sheet )
Pls clear me this, how can i do this.
Dear Chandoo, i want to match particular name ( Sheet 1 ) to name ( sheet 2) and after matching it has paste next column value( Sheet 2 ) to next column of name ( 1 sheet )
Pls clear me this, how can i do this.
What if the value in the cell is a date, and I want to match just the day and month (exclude the year)? For example, today is 12/9/10 and every year on 12/9 a donation of $100 is made to St Judes children hospital. Would the match function be able to compare today’s month and day to the dates within a table of annual expenses? (without needing to separate the month and day from the date into two columns)
Hi hellomoto:
I think this may work:
=INDEX(B2:C9,MATCH(F3&G3,(DAY(B2:B9)&MONTH(B2:B9)),0),2)
enter with ctrl+alt+delete
Where:
B2:C9 is the array where the data is
F3&G3 are the cells day and month that you want data for
Hope it works
Mike,
What did you mean by enter with ctrl+alt+delete?
I tried that formula but got the #value error. BTW, using excel 2003.
Hi,
sorry it should have been “ctrl+alt+enter” wasn’t thinking when i replied
Should work for 2003. Pity I can’t upload an example.
The formuale needs to be converted to array type formulae. This is done by entering the formulae as normal and then place your cursor in the formulae bar and entering “ctrl+alt+enter” all at the same time.
Mike
It should be CTRL+SHIFT+ENTER
ctrl+shift+enter —-> please tell me more about it i am lost in it and cant be choked forever without knowing it
– Sushil
sushil,
ctrl+shift+enter refers to a combination of keys you press just like when you do crtl+alt+delete to reset your computer
in this case it will “unlock” the array function of the formula also known as formula validation.
If you clic the cell you will then see that the formula is now between {…}.
This type of formula needs a validation process which is what the key sequence actually does. If you need to modify the formula, you can, but remember to “validate” it again once you are done.
– Gabriel
Mike and Chandoo, in the formula is F3&G3. Please explain the ‘&’. Is it short hand for AND? I searched Excel help files to get the meaning of &, but no hits.
BTW, I am using 2007 now. In a few months, I will get 2010 🙂
Thx
=CONCATENATE(“a”,”b”,”c”) ? =”a”&”b”&”c”
? ==> I Wrote eqvialent ( The special sing)
& is used to concatenate 2 values. So if F3 has “a” and G3 has “b”, F3&G3 will be “ab”.
Thought you were on vacation now 🙂 Thanks for the quick repsonse. I’ll look up concatenate to further understand what’s going on. Ultimately, I am just looking for a simple way (via formula) to look up data by month and day. I am working on a little personal budget worksheet. Because Excel assigns unique numbers to dates, I am using two columns for the date. One column formatted to show month and second to show day. Then using Vlookup and IF to pull a cell value from a table of due dates (day and month in 2 separate columns). To keep this short, I want use a single column for dates. I posted this question in the forum, but have not found a good solution yet.
Thx in advance, Happy holidays!
Yes, I’ve calculated the last homework by the sumproduct as below:
=MAX(D5:D17)/(SUMPRODUCT(((D5:D17)=(MAX(D5:D17)))+0,C5:C17))-MIN(D5:D17)/(SUMPRODUCT(((D5:D17)=(MIN(D5:D17)))+0,C5:C17))
Looks a little bit complicated, but the same result as by using index. Seems sumproduct is powerful, thanks for Chandoo’s another post regarding sumproduct.
HI CHANDOO,
IT’S REALY VERY GOOD AND HOW CAN I GET TUTORIAL FOR THE SAME SO THAT I CHOULD LEARN THIS TYPE OF THINGS, I M VERY HAPPY TO SEE THIS TYPE OF REPORTS , WHICH COULD BE BENEFICIAL FOR EVERY BODY.
Hi,
have the same question earlier asked by Arman, what if the value looking for is present in two cells, in other words, what if there are more than 1 person who has sales of 1088? How to show all results?
Just went through the homework and wanted to post it, @ Jason, i made a couple corrections to your formulas.
Homework:
1. =INDEX(B5:B17,MATCH(LARGE(D5:D17,2),$D$5:$D$17,0))
2. =RANK(INDEX(F5:F17,MATCH(LARGE(D5:D17,2),D5:D17,0)),F5:F17)
3. =LARGE(D5:D17,1)/INDEX(C5:C17,MATCH(LARGE(D5:D17,1),D5:D17,0))-(SMALL(D5:D17,1)/INDEX(C5:C17,MATCH(SMALL(D5:D17,1),D5:D17,0)))
thanks a lot….
Hi Pratick,
What do the values 2 and 0 stand for in the closed parentheses?
The second largest
Hi Chandoo,
I am novice in Excel. Hope you can guide me to correct excel formula. I have a spreadsheet “1” with data for different countries as follows:
A B
1 Iran 150
2 Bolivia 100
3 India 500
4 Malaysia 1000
Now I want arrange data in a “separate spreadsheet” (lets say: 2) with countries sorted in my own order (considering there are ). Can you let me know what formula works for the same. Thank you.
@GSS
I would add a helper column and use the Rank function to add a Rank value
Then you can use a Vlookup to retrieve the values that match the ranks 1..x each in separate rows
I have a budget, with consultants’ names, wether or not they are inhouse (yes/no), then 12 columns for time spend in every month. In a Data sheet (with among other information) their hourly rate, which varies per month. I need to calculate in sheet 1, as a total “below” the sum of hours per month, the cost of the External consultants and the cost of my inhouse consultants as seperate subtotals. So far I have :{=SUM(IF($H$52:$H$74=”no”,L$52:L$74*(VLOOKUP($E87:$E108,’Data Sheet’!$B$3:$R$36,HLOOKUP(‘Data Sheet’!G$1,’Consult Check’!L$1:W$1,1,0)-39,0))))}; column H defines Inhouse Yes/No; column L = month 1’s hours; column E = Consultant’s name; Data sheet row 1 is a number of the month as this is an ongoing budget for more than one year, matching it with the number in my current sheet, to ‘extract’ the correct month’s hourly cost. Unfortunately, the formula uses the first consultant’s cost and not the specific consultant’s cost. Other than “manually” selecting each consultant’s hours and the corresponding rate, what formula can I use?
to add: there are several projects and several consultants working on them, some off course on more than one project per month. As the ‘sales manager/director’ have to complete this section, I want to ‘bullet proof’ the formulas so that in case of any changes made, different consultant selected than ‘version 1’, I do not have to ‘redo’ the specific formulas to ensure the costing is correct. I would also prefer not to have to add an additional sheet to do these calculations on.
Hi all, I’m wondering if one of you can help me with a similar type of formula I can’t seem to get to work. I’m working between 2 sheets and need to find a value from sheet 1, column a that will be inserted into sheet 2, column b. it is not numeric, it’s a text/general field. the qualifier for it is that i need it to be when it is between a date range, columns c and e on sheet 2, and equals values in fields j and k.
what i would be looking for is to get the value from A:A from sheet one to populate into sheet 2 column g2 when the values of sheet 2 columns j2 and k2 equal the values in sheet 1 D? and E?, and the date in sheet 1 E:E is equal to or between fields c2 and e2 on sheet 2.
thanks in advance
Please note that the index and match is beautiful, but when it comes to sorting, that formula will choke as its reference points will be all messed up…
Chandoo,
The below formula isn’t working if there are multiple sales people achieving the same net sales. Can you suggest a solution?
=INDEX($B$5:$B$17,MATCH(1088,$D$5:$D$17,0))
If you do VLOOKUP doesnt work either : VLOOKUP(1088,CHOOSE({1,2}, $B$5:$B$17,$D$5:$D$17),2,0) , it will only give one result if two 1088 are given
Can someone please help me and Sophia?
Ania
Hello,
Great site, lot’s of info. Did you, by any chnace, get the soltuion to this one? I want to return multiple values (in a list)…
I have a drop down menu in Column A of different subsidiaries and I would like to create a formula for if in Column A = Company A + Hours (Comun E) = Total
Dear Chandoo
Chandoo.org is one of the best Site, i am loving it ! i just happen to do rnd on the said formula and i found that not only we can do vlookup from left to right but also across spread sheet , awesome 🙂 sample file for your reference http://speedy.sh/2tyra/lookup-across-spreadsheet.xlsx
thanks a ton for this awesome world dedicated to excel 🙂
Hi,
Can we get the answer to the questions asked in the workbook:
1. Who sold second highest?
2. What is the Profit Ratio rank of person who sold second highest?
3. What is difference in sale per customer between the highest selling & lowest selling sales persons?
With the formulas used as well.
Thanks.
Anurag Singh
Please disregard my earlier message just saw the answers in comments.
VLOOKUP(1088,CHOOSE({2,1},$B$5:$B$17,$D$5:$D$17),2,0)
Can somebody please provide explanation for this formula provided by Mike which can used for VLOOKUP on left.
This is what I’ve found regarding
VLOOKUP(1088,CHOOSE({2,1},$B$5:$B$17,$D$5:$D$17),2,0)
It can be simplified as follows:
VLOOKUP(value_a, CHOOSE({column_index_of_value_a,column_index_of_return_value},column_value1,column_value2),2,0)
So, in this case, you wanted to find value 1088 which is in $D$5:$D$17 (column index 2) and return the corresponding value in $B$5:$B$17 (column index 1)
Hope this helps
Also, you can look at it like this:
CHOOSE({2,1},$B$5:$B$17,$D$5:$D$17)
CHOOSE function will create an array of $D$5:$D$17 (Net Sales) as the 1st column and $B$5:$B$17 (Sales Person) as the 2nd column.
So, basically VLOOKUP still searches from left to right.
Hi Kampungguy, Thanks so much! You’ve helped me to understand this formulas!!! ^^
Hi Chandoo.
I just want to ask how you make a list of names who for example failed in a particular exam. I have a list of 50 students in column A and their marks in column B. Some of them got the failing mark of 70%. How do make a list of these students who got failing marks in a separate worksheet. What formula will i use which would automatically determine and list the names on the basis of such criteria. Thanks!
Hi i have gone through the different solutions but failed to get the solution for my problem. I have a table of data having date name and salary. Each salary revision will be added to the table as a new row and i need to find the salary of a person on a particular date. The result should be the salary of the particular employee on the maximum date less than the particular date. I am attaching the data here. I appreciate for you kind help on this..
EffectiveDate
Name
Salary
01-Jan-2012
Jason
10000
01-Jan-2012
Robin
12500
01-Jan-2012
Paul
11000
01-Mar-2012
John
15000
01-Mar-2012
Paul
11500
01-Mar-2012
Robin
15000
01-Jun-2012
John
20000
01-Jun-2012
Paul
12500
01-Jun-2012
Jason
15000
@Rinto
Can you post a sample file, Refer: http://chandoo.org/forums/topic/posting-a-sample-workbook
Dear Rinto,
run a macro wherein the 3 rows get transposed in columns and rest of the criteria would be easy to find either by using autofilters or by running a formula.
I cannot attach a sample file in this blog. Hope this would work.
regards,
H.Prashanth
Hi Chandu,
I have a query regarding grouping in excel.. I have a list of values (range of value). I have grouped them based on the category 0 to 50, 50 to 100, 100 to 150, 150 to 200 and so on. It works perfectly. Only issues that I face is when I update the value which falls under these range does not reflect under them instead they stand out separate. So, what I do is I ungroup and regroup based on the above condition. This activity is done every time when there is a value change or updation in data.
Is there a formula which can be used show the count to map the condition like.. value between 50 to 100 etc
Thanks,
Rajesh
hi Chandoo,
I think we can use =lookup(A1,{0,51,100},{“Fail”,”Pass”,”Pass”}) also.
Dear Chandoo,
I had gone through your formulae. But I think its not working like vlookup. Whenever I needs to find out any particular sell made by which salesman; I am not able to get if the data is voluminous. So I will require your help to deal with such a situations.
i have a table with Requestor names linked to cost center numbers. Each requestor and cost center are assigned to a Financial Analyst. What formula can i use to look up seversl requestors per cost center tied to a Financial Analyst?
1. Who sold second highest?
Answer: INDEX(B5:B17,MATCH(LARGE(D5:D17,2),D5:D17,0))
2. What is the Profit Ratio rank of person who sold second highest?
Answer: RANK(INDEX(F5:F17,MATCH(LARGE(D5:D17,2),D5:D17,0)),F5:F17)
3. 3. What is difference in sale per customer between the highest selling & lowest selling sales persons?
Answer: LARGE(D5:D17,1)/INDEX(C5:C17,MATCH(LARGE(D5:D17,1),D5:D17,0))-LARGE(D5:D17,2)/INDEX(C5:C17,MATCH(LARGE(D5:D17,2),D5:D17,0))
I believe that the task was to take into consideration lowest and highest sales and for the second part using LARGE(D5:D17,2) means you took into consideration the second highest value..
Hope i got it right because i havent used the large fct before
Hi,
Could you explain me the logic of the formula used for – 3. What is difference in sale per customer between the highest selling & lowest selling sales persons?
Thanks,
Kumar
@Kumar
I don’t believe the answer given above by Sandip is correct
I think it should be
=(MAX(D5:D17)/INDEX(C5:C17,MATCH(MAX(D5:D17),D5:D17,0)))-(MIN(D5:D17)/INDEX(C5:C17,MATCH(MIN(D5:D17),D5:D17,0)))
Which is easy to work through and understand
need to lookup a value in one sheet find in 2nd sheet then find text or value same as vlookup but too the left…
select value in column A in first sheet
lookup corresponding value in 2nd sheet in column U
find specific text or value to left
same as vlookup but to the left using 2 sheets…
got it using index… thx…
thank you so much. this is very help me ^_^
Hi, the column F contains 2 values with 36%. What do I do if I want to see what persons have ration of 36%?
Would I use the seperate funcions includiong LARGE?
I found reply to this type of questions, I saw it reoccuring many times. So for those hungry for an answer – what happens if there’s multiple values for the same reference?
Please look at that link, the unswer is under poing 4
http://chandoo.org/wp/2008/06/09/what-the-if-learn-6-cool-things-you-can-do-with-excel-if-functions/
Hello,
Thanks for the above but e.g. how does one provide a formula using index and match for a budget, and then bring back all the data in e.g. April for 2013 then compare same thing with April 13 budget so a snapshot can be provide analysing just the month’s Actuals and the Month’s budget?
Then a simple variance can be found on one sheet.
Many Thanks
@Mohammed
Are you able to post a sample file with some budget and actual data so that i can give you a specific answer
Refer: http://chandoo.org/forums/topic/posting-a-sample-workbook
How do I do a lookup with multiple criteria. My data looks something like this..
colmnA colmnb colmnc
Item state quantity
A CA 10
A IL 64
A AL 34
A OR 42
B IL 42
B CA 87
If I wanted to find out the quantity of item A in CA what formula would I use? Is this an index match match? An example formula would be great. Thanks.
@Matt
=Sumproduct((A1:A10=”A”)*(B1:B10=”CA”)*(C1:C10))
adjust ranges to suit
why not the lookup fuction its so much easier.
lookup(look up value, lookup vector, results vector)
plz make easy type solution and make me help
Hi Chandoo / All,
I am trying to use the index function, with the table array set as a defined name table. However, I am trying to have the table array syntax looking at a single cell which contains the named table and this is the data I want to use for my INDEX function.
Is this possible or is this a lost cause?
any help / advice welcome.
thank you!
Chandoo sir, it is good to see the lookup value to the left, but is there any way to setup two conditions for looking the value.
Like in case of sumifs, we can give more conditions na.
Help, I cannot find a forum that has already answered my query.
In Tab1 have names in column A. In Tab2 I have the sames names in column B but duplicated. In Tab 2 column C I have values.
Basically, In tab 1, I am trying to match the names from Tab2 ColumnB and return the highest value from Tab2 Column C.
Can anyone help me?
@Michelle… Welcome to Chandoo.org and thanks for your comments.
Assuming in tab1, the values are from A1,
You can use MAX formula to do this.
=MAX((tab2!$B$1:$B$100=tab1!A1)*(tab2!$c$1:$c$100))
Press CTRL+Shift+Enter after typing this formula.
If you have Excel 2010 or above, you can also use:
=AGGREGATE(14,4,(tab2!$B$1:$B$100=tab1!A1)*(tab2!$c$1:$c$100),1)
This can be entered without ctrl+shift.
Dear Chandoo,
How to use conditions in INDEX+MATCH function ?
Eg : I have Group in Column A, Co in Column B, Circle in Column C, and Vendor in Column D, Sales in Column E. Now matching all criteria s of Columns A,B,C & D the result will be Column E (Sales).
Please Help
Thanks in advance
@Imran
Can you please post a sample file
Hui…
Dear Hui,
Thanks for reply
DATA
Group Company Circle Vendor Sales
X A NORTH TTC 2000
Y B SOUTH ABC 3000
X A EAST TTC 6000
Y B WEST XYY 7000
RESULT
C -1 C-2 C-3 C-4 RESULT
Group Company Circle Vendor Sales
X A EAST TTC 6000
Now I have Criteria 1,2,3 & 4. I will check this criteria from above data and populate the sales figure with one formula. Hope You got my point.
Sample File:-
http://www.megafileupload.com/en/file/502782/sample-for-INDEX-MATCH-xlsx.html
@Imran
I am unsure what the relationship between C1, c2 etc is to the data
However why not use an Advanced Filter?
Use the Google Custom Search box at the Top Right of this page
@Hui
Thanks for your reply. I got solution for this. I have used “DGET” function instead of index+match. 🙂
Im using your below formula.. but when I click F2 and F9.. the formulas were gone.. how can retain the equation.
=INDEX(B2:K2,MATCH(MAX(COUNTIF(B2:K2,B2:K2)), COUNTIF(B2:K2,B2:K2),0))
& ” (“&MAX(COUNTIF(B2:K2,B2:K2))&” times)”
I have a range of columns containing formulas and numbers, headed 1 , 2, 3, etc.
I want to enter, say, “3” and get the range of fields in column 3 copied to another sheet.
I cannot see how to do this with VLOOKUP and am not sure how to do it.
Can anyone give me the formula to do this.. I am a newbie?
I have a database where there are 2 cities. Against one city i want rates to be applied on 0.5 Kg as 15 and if the consignment wt is 1 kg then rates applied are to be 20 which will carry on subsequent kgs.
However in case of city 2, the rates for 0.5 Kg is 20 and if the consginment wt is 1 kg then rate applied is to be 30.
Please suggest the formula with syntax to help me come out of this situation.
Hello. I have a doozy! I have a list of names in column A, a list of numbers in column B, and in column C I would like to produce a list of the names from column A that correspond to a specific number in column B, but I would like that list to not have any rows that are blank. Try this:
Column A (place one of these names in each row of column A): Ashley, Heather, Brian, Mike, Brad, Jim, Julie, Alex, Lynne, Janine
Column B (place one number in each row of column B): 1, 2, 1, 1, 2, 0, 2, 1, 0, 1
Column C (these are the results I’m looking for with a formula of some kind): Ashley, Brian, Mike, Alex, Janine
Please let me know if there’s something out there that will accomplish this! Thank you so much! I’ve looked everywhere. I can send you a spreadsheet if you need!
@Heather… I am not sure understand the logic behind how names are fetched in column C. How to interpret the numbers 1,2,1,1,2… to get the names Ashley, Brian, Mike, Alex, Janine..?
use this formula in cell C2: =IF(B2=””,””,A2), to get the name from A2, if B2 is NOT blank. Copy the formula to other cells.
please send me your query and answers
thanks
arvind
Hi Chando.
Appreciate your website. im not an expert though.
Can you further help me with my data please
@Amidala
I’d suggest posting questions at the Chandoo.org forums
http://chandoo.org/forum/
Hui…
Hi All,
Using Index+Match function can i return 2 or more values. As per the above example can i list sales person who have done >1000.
@Vikram
Have a read of:
http://chandoo.org/wp/2011/11/18/formula-forensics-003/
Hi.. I have a excel question..
If I have a data
A 700
B 0
C 800
D 0
and I would like to summarise the data to consist of the one that has value become this:
A 700
C 800
(with no B and D), how to make it that way?
Hi..I use the =INDEX($B$5:$B$17,MATCH(1088,$D$5:$D$17,0)), but $D$5:$D$17 has a data validation rule of defining a list of values already. Due to that i get an error “a user has restricted values that can be entered into this cell”.
How do i get rid of that error, without deleting the data validation ?
Please assist.
you can use if.
=IF(D2=1088;B2;””) ===> RESULT = JOHN.
then you copy the formula down.
this can be used to find anyone with the sales number of 2,088.
just that simple
What can Index Match do that vlookup can’t?
First let me start with that vlookup is a bit simpler and quicker to put together and has its time and place.
Sometimes though, we need to lookup a value that might be to the left of the search column (remember, vlookup only searches from left to right).
Rumour has it that Index Match also works faster in big spreadsheets but I don’t notice much of a difference.
Here’s the structure of the Index Match formula:
=index(WhichColumnToReturnValueFrom , (match(SearchForValue, SearchColumn, 0)
=INDEX ( Column I want a return value from , ( MATCH ( My Lookup Value , Column I want to Lookup against , ZeroForExactMatch)
So an example would be if we had a student name and wanted to look up their student number (column to the left) –
Source: http://www.excelvlookuphelp.com
@Matt
1. Lookup to the left
2. Correctly access unsorted data
whenever i am unclear what to do,whenever i am clue less,whenever i search for easier way to do things in excel i run google search that routes me to chandoo then he meets my expectations like ever.
it makes me feel like “we are gifted with such an intelligent”
Very stuck at the moment with index match, can you give me a hand?
I have a stocklist for my business, but we buy all our stock off a business who send us a stock sheet every day with a larger amount of products (including all the ones to be included in my stocklist but with additional products which do not need to be added) so let’s say my stocksheet is sheet 1, and the larger stocksheet which I will copy stockcount from is sheet 2; I require a formula that will match the product code from sheet 1 to sheet 2, and return the stock count from sheet 2 back to sheet 1 in the column ‘stock level’ which will appear to the right of the product code – I followed your tutorial and it returned ‘N/A’ after entering =INDEX(Sheet2!B1:B4177,MATCH(Sheet2!A1635,Sheet2!A1:A4177, 0))
Any help?? Thanks!!
Hi Matthew… thanks for your comments. My guess would be the stock code is not present in Sheet 2.
Here is what I suggest:
Also, make sure your references are absolute.
hi,
I need help…
I have data, with list of customers. lets say- 10 customers.
there are 10 projects per customer.
I want to lookup the project per customer. as the customer name is repeated in the master data, I can not use vlookup.
EX.
CustomerA- Project1
Project2
Project3
Project4… Project10
…
…
Project18
CutomerB- Proj1
Proj2
Now if I am selecting CustomerA it must show me all 10 Projects in the next column.
Awaiting for reply.
In case, If its required to share the data, please let me konw.
Regards,
Prajakta
Hello,
I am looking for help with writing a formula in an excel workbook.
On the first worksheet I am inputting for example:
Column A Column B Column C
Employee Name Budget Amount Budget Code
Amy 200.00 01.6500.0.5770
Bob 150.00 01.6510.0.5710
On worksheet 2 I have a lot more information, but would like the information from the first worksheet to auto populate the budget amount and budget code once I type the employee’s name.
The columns A, B & C on worksheet 2 are titled the same as worksheet one.
Can anyone help?
Never mind I figured it out. 🙂
Homework:
1.=INDEX($B$5:$B$17,MATCH(LARGE($D$5:$D$17,2),$D$5:$D$17,0))
2. =RANK(F15,INDEX(F5:F17,MATCH(LARGE(D5:D17,2),D5:D17,0)))
(The ranking of second highest is 3, but my formula get 1, please assist
to amend if anything goes wrong)
3.=INDEX(D5:D17,MATCH(MAX(D5:D17),D5:D17,0))/INDEX(C5:C17,MATCH(MAX(D5:D17),D5:D17,0))-INDEX(D5:D17,MATCH(MIN(D5:D17),D5:D17,0))/INDEX(C5:C17,MATCH(MIN(D5:D17),D5:D17,0))
At the 3rd question, my logic is use the INDEX + MATCH to find the highest selling sale per people and lowest sale per people. Use the highest – lowest and get the difference is 179.
Homework:
Finally, i found the correct way to get the answer.
2. =RANK(INDEX(F5:F17,MATCH(LARGE(D5:D17,2),D5:D17,0)),F5:F17)
Very informative article!
Forgive my ignorance here (only recently started diving into Excel), but when do you ever actually really need a formula to lookup values to the left? You’re always specifying a range, so VLOOKUP would have worked fine in the example table, if you specify that whole table as the range. Since you’re always specifying ranges, when do you ever really need to go left?
By this I mean that you can return a value from Column 1. So, yes, VLOOKUP can come to the rescue.
in “MATCH” Function there is limitation. if u have a table like duplicate values in column 1,2,5,7,9,2,6,7,…… if u apply match function for this first it will show row number 1 and u will drag for further rows
out put like; 1row,2row,3row,4row,5row,2row,7row,4row
the output value depends on what the input we have…
can you tell me about this solutions “chandoo” anna.
Dears,
I want a formula for table below
Asset Value Rate of Depeciation Amount of Depreciation
Car 500000 15% 75000
Building 1000000 10% 100000
Machine 700000 15% 105000
Furniture 100000 10% 10000
if i have two columns in another table
e. g. Asset and value
and I write car in cell A2 the cell B2 should automatically fill with its value 500000
hi bro,
Could you please help me to solve this.
i have 2 excel books. In first book i have 2 columns.In the first column i have names and second column i have countries working. I want to create a formula in second book such that if i type the name of the person i should get the countries working
hello,
i have a problem if for example
Item Price Starting Ending
dtf8693 100 1/10/2013 1/1/2014
dtf8693 110 2/1/2014 3/2/2014
the above represents the price list the below are sales i want to find what price that corresponds to item on a date that falls between to find the discount amount is there a formula
dtf8693 1 50 1/11/2013
dtf8693 1 55 5/1/2014
@Nour
Can you please ask the question in the Chandoo.org Forums
http://forum.chandoo.org/
Please attach a sample file to get a faster response
Good information right here!
How do I index match to create a unique list from a column that pulls from 2 other columns using left/mid/right?
Hi chandoo,
I have a problem!!
how to use FIFO method in excel. Is there any formula for FIFO(first in first out).
plz help me with sample file!!
@Aung
Have you read: http://chandoo.org/wp/2010/11/18/scheduling-variable-sources/
i want to use suitable formula for
match sheet1 cell value to sheet2 cell range and if value is correct then print sheet2 next column text with commas,
@Ghazi
Can you please ask the question in the Chandoo.org Forums
http://forum.chandoo.org/
Please attach a sample file to get a faster response
Hello,
One question. What if in this case ( if you want to find-out who made $1088) there is another person besides John? Does this formula only work for unique characters?
Thank you!
Homework Question 1:
1. =VLOOKUP(LARGE(D5:D17,2),CHOOSE({1,2},D5:D17,B5:B17),2,0)
1. {=INDEX(B5:B17,MATCH(1,(D5:D17=LARGE(D5:D17,2))*1,0))}
1. {=+VLOOKUP(TRUE,CHOOSE({1,2},(D5:D17=LARGE(D5:D17,2)),B5:B17),2,0)}
I really love Index Match! that is much better than vlookup formula! It far more practical, less bugs and integration with Name Manager as well!
Index Match for ever!
Chandoo..
For Question : Who sold to minimum number of customers?
What is the difference between the following two formula in tour excel attached.
Formula 1 : =INDEX($B$5:$B$17,MIN($C$5:$C$17))
ANS: Johnson
Formula 2: =INDEX($B$5:$B$17,MATCH(MIN($C$5:$C$17),$C$5:$C$17,0))
ANS : Jessy
Can you please let me know the exact use of MATCH function. why it is giving different ans.
Seller = Qty Sold
A = 5
B = 6
C = 8
A = 4
B = 7
C = 6
D = 20
E = 15
A = 5
C = 9
B = 2
E = 5
Q How to sum up the highest/lowest sales quantity achieved by respective salesman.
Highest Total Sales Qty & Name of Seller = ?
Lowest Total Sales Qty & Name of Seller = ?
Using index and match with max and min and sumif or sumproduct?
Please no array formula
@Khurrum
The latest version of Excel 365 have a MaxIf and MinIf functions
I’m not sure if they have made it to Excel 2016 yet
Hi, I’ve used array formula without helper column
14 & A {=CONCATENATE((MIN(SUMIF(B2:B13,B2:B13,C2:C13))),” & “,INDEX(B:B,IF(ISNUMBER(MIN(SUMIF(B2:B13,B2:B13,C2:C13))),ROW(C2:C13),””)))}
23 & A {=CONCATENATE((MAX(SUMIF(B2:B13,B2:B13,C2:C13))),” & “,INDEX(B:B,IF(ISNUMBER(MAX(SUMIF(B2:B13,B2:B13,C2:C13))),ROW(C2:C13),””)))}
EDITED:
14 & A
{=CONCATENATE(MIN(SUMIF(B2:B13,B2:B13,C2:C13)),” & “,VLOOKUP(MIN(SUMIF(B2:B13,B2:B13,C2:C13)),CHOOSE({2,1},B2:B13,SUMIF(B2:B13,B2:B13,C2:C13)),2,0))}
23 & C
{=CONCATENATE(MAX(SUMIF(B2:B13,B2:B13,C2:C13)),” & “,VLOOKUP(MAX(SUMIF(B2:B13,B2:B13,C2:C13)),CHOOSE({2,1},B2:B13,SUMIF(B2:B13,B2:B13,C2:C13)),2,0))}
Thanx Man1ak, it works well.
Hope it will work with index and match w/o array if there is some one who resolve it. It is not an argument but consideration for the user.
Np,
you are looking for solution with index and match?
Yes Man1ak, Index and Match is required since it will work with other queries as well
Thanx Man1ak
Your are quiet close but answer is
14 & A
23 & C
It is not a challenge but an honest query, do whatever it takes, without a helper column and kindly explain an array formula as well, once it works.
Hi Hui
Yes, it doesn’t available in Excel 2016 pro plus, 64 bit. as yet.
I tried with the helper column it works perfect, but it is not I am looking for since I don’t have enough room for helper column.
I want a single cell formula which gives name & amount both simultaneously.
LEFT VLOOKUP SOLUTIONS:
23 & C
{=CONCATENATE(LARGE(SUMIF(D7:D18,D7:D18,E7:E18),1),” & “,VLOOKUP(TRUE,CHOOSE({1,2},(SUMIF(D7:D18,D7:D18,E7:E18)=LARGE(SUMIF(D7:D18,D7:D18,E7:E18),1)),D7:D18),2,0))}
14 & A
{=CONCATENATE(SMALL(SUMIF(D7:D18,D7:D18,E7:E18),1),” & “,VLOOKUP(TRUE,CHOOSE({1,2},(SUMIF(D7:D18,D7:D18,E7:E18)=SMALL(SUMIF(D7:D18,D7:D18,E7:E18),1)),D7:D18),2,0))}
can I combine these two separate index match Fx into one.
=INDEX($D:$D,MATCH(I51,$E:$E,0))
=INDEX($A:$A,MATCH(I51,$B:$B,0))
220100013145
5901551367
1342010037890
18401518190
2531530002616
4520100014705
48010100197915
70900101001792
87501000001973
10075966653
11204964054
12481600000011
1488101001433
1488101051471
3011151082
30704276397
543102010004325
6226000100023040
624001532102
624201525768
624201526434
874010100012330
how to get spaces in suffix of numbers maximum to 17 digit
Hi There,
I have the data with like below.
Dimensions are in different columns, however KPI/values columns are consistent. Could you please advise how to do lookup ?
I’m using concatenate (4 dimensions) and then lookup but it is increasing file size as i have the data till 50k rows.
Is there any other way to do it ?
Please advise.
country city device month sales units
UK London All Feb 200 20
US California All Feb 600 60
city month device country sales units
London Feb All UK 200 20
California Feb All US 600 60
Thanks,
Chandu
All,
I see that Question 3 resulted in (1) Answer, however that is not fully correct. It was, “Who sold to the minimum number of customers?” There are (2) Answers for this question. Jessy and Jimmy both sold to (6) customers. However you formula produced only one answer of Jessy. How do you get a formula that produces both Jessy and Jimmy?
Thanks,
how can we enter a more then 15 digit a number without making the cell in text format , and how can we change this digit limit..
@Jatin
You can’t change the Excel 15 digit limit without re-writing Excel and I sort of doubt you have the source code
If you want to deal with longer numbers, You should read:
http://dailydoseofexcel.com/archives/2009/01/12/large-number-arithmetic/
I need help in excel, i have huge data i want formulae such that sum of 3 variables match a total but the 3 variables should be in the range of 300 to 800.
for example:
target: 2200
A=?
B=?
C=?
i want formulaes in the place of ? such that the sum(A+B+C) matches the target. Also the condition is A,B and C should be any number between 300 to 800.
Please help.
I need Excel to select a value depending on a random coin flip. The values are: 5, 10, 15, 20, 25, … through 95. The beginning number is always 10. If the coin returns tails the number returned will always increment one level (plus 5, but never greater than 95). If the coin returns heads the number returned will always decrement one level (minus 5, but never less than 5). However, if the coin returns heads two out of any three consecutive tosses the number will be set to 10 and everything starts over.
I’d like to perform this with INDEX/MATCH or VLOOKUP if possible. I’ve been able to get most of it, but the “two out of any three consecutive tosses” has got me stumped.
Thanx,
Ken
@Ken
Please ask the question in the Chandoo.org Forums
http://forum.chandoo.org/
Attach a sample file for a more specific answer
Homework answers
=INDEX($B$5:$B$17,MATCH(LARGE($D$5:$D$17,2),$D$5:$D$17,0))
=RANK(INDEX($F$5:$F$17,MATCH(LARGE($D$5:$D$17,2),$D$5:$D$17,0)), F5:F17)
=INDEX($D$5:$D$17,MATCH(MAX($D$5:$D$17),$D$5:$D$17,0)) /INDEX($C$5:$C$17,MATCH(MAX($D$5:$D$17),$D$5:$D$17,0))-INDEX($D$5:$D$17,MATCH(MIN($D$5:$D$17),$D$5:$D$17,0)) /INDEX($C$5:$C$17,MATCH(MIN($C$5:$C$17),$C$5:$C$17,0))
How to use Index function if i want to return entire rows that contain the name i am looking for?
@Jansaya
Please ask the question in the Chandoo.org Forums
https://chandoo.org/forum/
Please attach a sample file with an expected solution
1.=INDEX(B5:B17,MATCH(LARGE(D5:D17,2),D5:D17,0),1)
2.=RANK(INDEX(F5:F17,MATCH(LARGE(D5:D17,2),D5:D17,0),1),F5:F17,0)
3.=INDEX(D5:D17,MATCH(MAX(D5:D17),D5:D17,0),1)/INDEX(C5:C17,MATCH(MAX(D5:D17),D5:D17,0),1)-INDEX(D5:D17,MATCH(MIN(D5:D17),D5:D17,0),1)/INDEX(C5:C17,MATCH(MIN(D5:D17),D5:D17,0),1)
1.=LOOKUP(LARGE(D5:D17,2),$D$5:$D$17,B4:B17)
2.=RANK(VLOOKUP(J12,B5:F17,5,FALSE),F5:F17,0)
3.=(MAX(D5:D17)/LOOKUP(MAX(D5:D17),D5:D17,C5:C17))-(SMALL(D5:D17,1)/INDEX(C5:C17,MATCH(MIN(D5:D17),D5:D17,0)))
Hi Chandoo, I am having trouble in using INDEX+MATCH. I am practicing with a sample file to find the entire sales figure for 1 category of product. But whatever combination I try, I just get #N/A. Please help me out.
Hi Chandoo, could you please help me.
I have problems with Index and Match when it comes to different tables, example:
Data tables belonging to different countries.
I have an example file. How do I send to you so you could see where I am stuck? thanks!
what will be the formula for naming those people whose sales are greater than 0 or a particular number
1. Who sold second highest?
2. What is the Profit Ratio rank of person who sold second highest?
3. What is difference in sale per customer between the highest selling & lowest selling sales persons?
Not able to get out of this one.
Please help how can be index match will be used to get this one
I have 2 sheets with addresses. 1 is from fleet software that has address where parked, not exact address of job. Have a job address list that has job # and address. On the trip sheet I need to have a column fill in job # looking up the address from the job sheet and finding the street or closest.