Question description:
This user has given permission to use the problem statement for this
blog.
I have a file with two sheets. On the 1st, I have dollars in O13and in O16 a number signifying 1-6 people.
On the second workbook is a matrix that corresponds to the dollar amount AND the number of people. For example, sheet 1 O13 is $6,000. O16 is 3. I want a formula to find $6,000 (not higher) on sheet 2 in column A. Then I want it to compare sheet 1 O16 to the correct column (B-G representing 1-6) on sheet 2 for an exact match to place in sheet1 O17.
The range of sheet1 O13 is referenced on sheet2 A5-A383, and sheet 1 O16 is referenced on sheet2 B5:G383.
I have an INDEX Match but it isn't quite working, yet.
Solved by B. B. in 60 mins
This is the chat thread from the real Excelchat help session. It contains no private user
information.
Excelchat Expert
14/09/2018 - 04:46
Welcome to excelchat, I see your question is about a Index/Match formula.
Excelchat Expert
14/09/2018 - 04:46
I can help you with the problem. I'll also provide a full explanation of the solution.
Excelchat Expert
14/09/2018 - 04:47
Before we get started, this is a reminder that our policy is 1 problem per session with additional Q&A on that problem as time allows.
Excelchat Expert
14/09/2018 - 04:47
Let me ask you a couple of quick questions to make sure I fully understand your problem. So, let's start when you are ready..
User
14/09/2018 - 04:48
You bet. Let's do this.
Excelchat Expert
14/09/2018 - 04:49
Great! Can you please show me the sample data on the blank spreadsheet at the right hand side?
User
14/09/2018 - 04:50
Yes, and actually, I didn't have access to the file, but I do now. Would that be easier to upload?
Excelchat Expert
14/09/2018 - 04:51
To upload the file, please click on the paperclip icon then select the file and hit ENTER.
User
14/09/2018 - 04:52
Never mind it won't give me access.
User
14/09/2018 - 04:52
Here you go
Excelchat Expert
14/09/2018 - 04:52
Okay, let me know when you are done putting it together.
User
14/09/2018 - 04:55
Done. On sheet 1 the data in B3 will change from 1-6, depending on what the user inputs.
User
14/09/2018 - 04:55
Sorry, I meant B1
User
14/09/2018 - 04:55
The data in B2 will change, based on input from the user.
User
14/09/2018 - 04:56
With those two numbers, I need it to cross reference to sheet 2 to find the corresponding dollar amount to fill in B3.
Excelchat Expert
14/09/2018 - 04:56
Got it.
User
14/09/2018 - 04:56
In the example of 6,000 and 3, the return number should be 170 and fill into B3.
Excelchat Expert
14/09/2018 - 04:56
Understood.
User
14/09/2018 - 04:57
Ive used a INDEX MATCH to find something similar on a different part of the form, and it works like a charm. The difference is that it has one column to search and one column has the results, not 6 different columns that match the one 6,000 row
Excelchat Expert
14/09/2018 - 04:58
Based on what you’ve shared, you need the value from the matrix at B2:G15 range by matching Column A with amount and row 1 with number of people . Do you believe that will address your problem?
User
14/09/2018 - 05:00
Yes, it should. If either of those values change, it'll pick a different balue out of B2:g15. The data I have is greater. It ranges from $0-$20,000 in $50 increments. Additionally, the very first A2 would actually be 0-5000. I've updated the example.
User
14/09/2018 - 05:01
So if the number the person inputs is under 5,000, it should default to row 2 and then correspond to the correct number of people.
Excelchat Expert
14/09/2018 - 05:01
Okay. If the formula works on the example range, it will also work for bigger data set.
User
14/09/2018 - 05:01
Makes sense.
Excelchat Expert
14/09/2018 - 05:01
Please extend time by selecting Yes when you are asked. We need more time.
Excelchat Expert
14/09/2018 - 05:02
"So if the number the person inputs is under 5,000, it should default to row 2 and then correspond to the correct number of people.", sorry I don't understand this.
User
14/09/2018 - 05:04
The input dollar amount can be below 5,000, so if the person inputting dollar amounts puts 4,000, it should use the range in A2, since it doesn't meet A3. If they enter 6,020, it should pull from A12, since it is higher than 6,000 but lower than 6,100.
Excelchat Expert
14/09/2018 - 05:06
SO essentially column A values represents a range of amounts, not a particular amount?
User
14/09/2018 - 05:06
Correct. At least $xxx, but less than $xxxx
Excelchat Expert
14/09/2018 - 05:08
Ah I see what you mean.
Excelchat Expert
14/09/2018 - 05:08
I would need 15 to 20 minutes to write the necessary formula in B3 of sheet1.
Excelchat Expert
14/09/2018 - 05:08
Please stay with me..
User
14/09/2018 - 05:08
Ok. If you're able to solve this, I'll share your site with everyone I know!
Excelchat Expert
14/09/2018 - 05:10
Please be assured that I can solve this. It is not a question of if, but when. :D
User
14/09/2018 - 05:10
ha!
Excelchat Expert
14/09/2018 - 05:11
If I need more than 15 minutes, you would have to extend time once again.
User
14/09/2018 - 05:11
Ok
User
14/09/2018 - 05:11
I'll keep an eye on it.
Excelchat Expert
14/09/2018 - 05:11
Thanks.
Excelchat Expert
14/09/2018 - 05:15
Actually we can use a simple VLOOKUP formula if we can reorganize the values of column A a little bit.
User
14/09/2018 - 05:16
Possibly, The second sheet is coming from a different organization, so if it changes, the data from them may need to change.
User
14/09/2018 - 05:16
But I'm interested...
Excelchat Expert
14/09/2018 - 05:16
Just a minor change.
Excelchat Expert
14/09/2018 - 05:16
So, in stead of listing the upper limit of a range, we need to list the lower limit.
Excelchat Expert
14/09/2018 - 05:17
So for example, in A3, we write 0.
Excelchat Expert
14/09/2018 - 05:17
A4 we write 5099
User
14/09/2018 - 05:18
Oh, so everything would need to change?
Excelchat Expert
14/09/2018 - 05:18
Everything on column A, yeah.
User
14/09/2018 - 05:19
So the dollar amounts would likely rarely be an exact match to say, 6,000 It would return with 6,001.29 or even 6,000.01
User
14/09/2018 - 05:19
Would that make a sdifference?
Excelchat Expert
14/09/2018 - 05:20
We are rearranging for this exact reason.
Excelchat Expert
14/09/2018 - 05:21
VLOOKUP can do a partial match if the data is sorted.
User
14/09/2018 - 05:22
OK...
Excelchat Expert
14/09/2018 - 05:26
=VLOOKUP(B2,Sheet2!A:G,MATCH(B1,Sheet2!A1:G1,0),TRUE) I have tried this formula on existing data and it is working.
Excelchat Expert
14/09/2018 - 05:26
Please see B3 by changing B1 and B2.
User
14/09/2018 - 05:27
So inserting a blank line and changing B3 to 0?
User
14/09/2018 - 05:27
Mind if I type this up on the page and see if it works correctly?
Excelchat Expert
14/09/2018 - 05:28
We doesn't have to insert the blank line.
Excelchat Expert
14/09/2018 - 05:28
Just change the first value to 0 and check it on your file.
User
14/09/2018 - 05:29
Ok. Typing it in now.
Excelchat Expert
14/09/2018 - 05:29
Thanks, take your time.
User
14/09/2018 - 05:32
I got an #N/A
Excelchat Expert
14/09/2018 - 05:32
May I know for which input?
User
14/09/2018 - 05:33
How can I tell?
Excelchat Expert
14/09/2018 - 05:33
Okay, please show me the formula you wrote in your file.
Excelchat Expert
14/09/2018 - 05:34
Please note that this is the last extension. So we only have 12 minutes remaining..
User
14/09/2018 - 05:35
=VLOOKUP(O13,"Support Schedule'!A:G,MATCH(O16,"Support Schedule'!A1:G1,0),TRUE)
Excelchat Expert
14/09/2018 - 05:35
In support schedule sheet, your data range is A:G?
User
14/09/2018 - 05:36
Column A is the dollar amount that sheet 1 is trying to find. Just like this page 2 that we have
User
14/09/2018 - 05:36
But it goes from lines 5-383.
User
14/09/2018 - 05:36
rows...
Excelchat Expert
14/09/2018 - 05:37
Please try this =VLOOKUP(O13,"Support Schedule'!A:G,MATCH(O16,"Support Schedule'!A5:G5,0),TRUE)
Excelchat Expert
14/09/2018 - 05:39
Does this solution solve your problem?
Excelchat Expert
14/09/2018 - 05:42
Just 4 more minutes remaining.
Excelchat Expert
14/09/2018 - 05:42
Do you have any other questions about this problem and the solution?
Excelchat Expert
14/09/2018 - 05:43
The chat will end soon.
Excelchat Expert
14/09/2018 - 05:44
Thanks for coming to Excelchat. Feel free to leave any comments or feedback. Have a nice day!
This is the output file from the real Excelchat help session:
This is an example of the expert help you can get. It contains no private user
information.
