Excel - INDEX MATCH Function - Expert Solution

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
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
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
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
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.

Get instant expert help with Excel and Google Sheets

Post your problem and you’ll get expert help in seconds.

Your message must be at least 40 characters
Your privacy is guaranteed. Your session will not be used for blog unless you give us persmission.