I am stumped and am not sure if a formula or macro is the solution.
If a user enters a value in cell A1 (ex. $1150)
I want Cell A2 to read this quantity and apply the appropriate value from a separate data table heading that is rounded up.
If the data table does not have a value for $1150 it applies the information from the header reading $1200.
Can someone give me some guidance here?
Last edited by NSchipper; 02-19-2016 at 02:35 PM. Reason: Workbook Adition
Welcome to the board.
Please take a few minutes to read the forum rules, and then amend your thread title accordingly.
Thanks.
Entia non sunt multiplicanda sine necessitate
Upon further review, an INDEX or MATCH function may work here, but I am unsure on how to write it.
Cell A1 - $1150
Then a data set is indexed for this column and the value from a specific row in that data set is returned.
The challenge is if the value is changed that the returning value changes with it.
Any thoughts?
Perhaps you could post a workbook with an example and desired results.
Workbook Uploaded, for reference cell C9 needs to search the data set array L7:AV7 for the value placed by the user in cell C5 and return the appropriate result from that column/row intersection.
You have used the VLOOKUP() function for horizontally oriented data. In this case, you would use the HLOOKUP() function (https://support.office.com/en-us/art...__toc309306714 ). Or use the MATCH() function as I explain in the other thread (http://www.excelforum.com/showthread...40#post4321440 )
Originally Posted by shg
After you unmerge all of the merged cells, in C9,
=HLOOKUP(C5, L7:AV9, 3)
that works, is there a way to factor in rounding up vs. down? for example, if the user enters $1175 can the sheet reference the value for $1250?
If the data were sorted in descending order, you could simply use the MATCH() function with the third argument set to -1.
As it is, you can still use the MATCH() function (third argument set to 1), then add one to the function's result. =MATCH($C$5,$L$7:$AV$9,1)+1 --> INDEX(l10:av10,match result).
Mr. Shorty/shg - if I use the HLOOKUP function, is there a way to search for a rounded up data set? I do not need a rounded up result (I can handle that through formatting). The data set starts at $1000 then moves to $1250.
If a user enters $1100 can this be directed to the next value UP ex. 1250 as opposed to referencing the lowest total ex. $1000?
Did you look at using the MATCH() function (coupled with the INDEX() function) as I explained? If I am understanding what you want, that should work just fine.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks