Dear All,
I hope someone can help me again.
In the sheet named "Salary", column A2 and A3.... contains an amount, a formula in column B2 should find the cell A2 in the sheet named "Medical Scale" and return the adjacent cell to the right. As i attached a sample file for reference.
Thank you in advance.
Corine
corinereyes
If you can accept VBA, attached has Macro that covers all 903 matches almost instantly for you:
Ochimus
Please Login or Register to view this content.
There's a problem with your examples since there are presumably intended results in Salary!B2 and Salary!B3. Salary!B2 seems to depend on 'Medical Scale'!B12:AC12, but Salary!B3 seems to depend on 'Medical Scale'!B11:AC11. Indeed, in the Medical Scale worksheet, cells B7, G8 and V13 are all 13,310. There's NO WAY to distinguish values in Salary column A by distinct entries in 'Medical Scale'!B3:AC13. You need to have some sort of indicator in the Salary worksheet for type of position group (values in 'Medical Scale'!A3:A13). Without such indicators, it's unreliable just to pick Salary!A:A values in 'Medical Scale'!B3:AB13.
I aggree with hrlngrv. I guess there must be another criteria for job title, i.e,
"looking for next salary of 13310 of "Senior Pharmacist" position" it should be 13785 (cell C7) not 13785 (H8)
Quang PT
Must be missing something here, because you both quote examples where the value in Col A may have multiple offsets, but they are all the same value.
If a value in Col A had different offsets I could understand the need for an additional criteria. But why does it matter which one the code or formula finds if all the offsets to the value in Col A are identical?
Ochimus
B2 , Array formula
HTML Code:
You are.Originally Posted by Ochimus
In the Medical Scale worksheet, cells B7, G8 and V13 all evaluate to 13,310. That'd be no big deal IF the same number were in the cell immediately to the right of each. While C7 and H8 are both 13,785, W13 is 13,815. How do you believe it'd be possible to tell which Salary!A:A cells evaluating to 13,310 should match that value in Medical Scale's row 7, row 8 or row 13? Note also in Medical Scale, I6 and N9 are 19,990, but J6 is 20,670 while O9 is 20,610.
The problem is underparametrized.
Last edited by hrlngrv; 11-28-2020 at 03:12 AM. Reason: typos
No need for INDIRECT, never need for INDIRECT(ADDRESS(...)).
IF the problem were adequately parametrized, also an array formula,
Formula:
Please Login or Register to view this content.
Dear Ochimus,
I have tried this code and it works perfect! I will use it in my future projects. Thank you so much.
Dear hrlngrv,
I have thought about it also, i have tried index match sumproduct up to the extent of my knowledge in excel but non of them worked so i asked to group for a quick solution. thank you so much for your effort.
formula works perfectly fine! thank you so much for your help.No need for INDIRECT, never need for INDIRECT(ADDRESS(...)).
IF the problem were adequately parametrized, also an array formula,
Formula:Please Login or Register to view this content.
Thank you all for all your efforts.
If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED.
Administrative Note:
Please don't quote whole posts, especially when you are responding to the one immediately preceding your own - it's just clutter. It's OK to quote if you are responding to a post out of sequence, but limit quoted content to a few relevant lines that makes clear to whom and what you are responding. Thanks!
For normal conversational replies, try using the QUICK REPLY box below or the REPLY button instead of REPLY WITH QUOTE.
Ali
Enthusiastic self-taught user of MS Excel who's always learning!
Don't forget to say "thank you" in your thread to anyone who has offered you help.
You can reward them by clicking on * Add Reputation below their user name on the left, if you wish.
Forum Rules (updated August 2023): please read them here.
It doesn't work fine.
The good news is that Level 6 Grade 1 Medical Assistants who had been earning 13,310 and were promoted to Level 6 Grade 2 most assuredly will complain immediately that their new pay isn't 13,815. On the other hand, Level 1 Grade 1 Senior Pharmacists and Level 2 Grade 2 Pharmacists who had been making 13.310 may not complain at all if on promotion to the next higher grade their pay rose to 13,815 rather than 13,785.
You are fooling yourself if you believe any of the solutions offered are RELIABLE for ALL circumstances you're likely to experience in this workbook.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks