• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

VLOOKUP Query

J

Jaine Wills

New Member
Hi

I've posted a data validation query but I also have one re Vlookups.

I have a range of possibles and I want to use the range as a lookup - there will be only one entry over a range of say, 5 possibles - the table array contains everything I need and the return field is ass normal. The only difference top a normal lookup is that fact that the lookup is not a single cell. Can I do this?

Thanks :)
 
Hi Bobhc

Hope this works :)

The field is on sheet one, and the data I want to look up is on sheet 3 (column 3).

Thanks
 

Attachments

  • Example.xlsx
    13.6 KB · Views: 20
Hi Jaine,
Welcome to the forum!

I am not sure I understand what you are looking for... are you wanting to lookup the codes in cells M4, N4, O4 and return the weekly rate from Sheet3 column 3, so as to calculate the award amount?

If so, try the following formula in cell S4
=IFERROR(VLOOKUP(LOOKUP(1,SEARCH("*",M4:O4),M4:O4),Sheet3!$A$3:$C$21,3,FALSE),0)*52

(The formula takes the last of any multiple codes found in M4:O4. If you need to add up the award amounts of multiple codes, the formula will need to be tweaked.)

If not, please describe the problem a little more. It would be best if you can provide a manually calculated example.

Cheers,
Sajan.
 
Janie

Sajan's formula does what you want and returns the value for the year,......but you have in column L data validation of "Overturned" "Withdrawn" and "Upheld", if the appeal is Overturned or Withdrawn then would the Award in S column be nothing?
 
Hi Bob

Yes, that is correct. Cells M4:Q4 would only be populated if the data in L is "overturned". The team would have paperwork in front of them to see that the decision had been overturned and what the particular award is. Just to add to the problem (lol) if the award is for longer than a year then the number of years in R also needs to be applied. I think it will be too much to add the if...upheld.."" for example, so I'll have to trust them :)

Furthermore, if you have a look on the sheet you will see there are multiple possibilities for DLA and PIP. Due to the way the benefit is structured, it is possible to have an entry in columns M and N or in P and Q (but not all of them, or M and P for example).

Sajan's formula did indeed work, and I was delighted and started getting really excited, but then I changed the variables and it did not change the amount at the end.
 
Hello Jaine,
Can you elaborate on the results you got when you tried my formula above? That would help us to help you better.

It would be best if you can describe all of your requirements in one place. (From another post of yours, I saw a comment about a maximum of two input codes that will need to be looked up on the second sheet. Is that so? What other requirements exist with your data? Also, please note that you may have to explain any domain specific information that is relevant to your solution. I do not have any experience with the legal system, let alone with one in the UK. I was merely guessing as to what you wanted when I supplied the formula above. It would be best if you are able to confirm your needs.)

Since you indicated that you were once proficient in Excel, I am sure you will agree that once the problem/requirements are defined, we should be able to determine a solution.

Let us know if you need further assistance.

Cheers,
Sajan.
 
Thanks Sajan. I appreciate your help. I've uploaded a fuller sample, slightly abridged, with commented cells for explanation. Good luck!

Many thanks :confused:
 
Hello Jaine,
I will look at the uploaded file and come back with any questions.

Cheers,
Sajan.
 
Hello Jaine,
I have added a new formula in the "Forum Formula" column:
=SUM(MMULT(N(AwardCodes=$M5:$Q5), TRANSPOSE(COLUMN($M5:$Q5)^0))*AwardRates)*$R5*52

entered with Ctrl + Shift + Enter

I have also added some condition formatting on the NI column, based on a helper cell called "NI Alerts". In addition, I have also added an "NI Alert Message" column that displays a message if the NI number is not in the correct format.

Play around with it, and let me know if you encounter any additional scenarios that need to be added.

Cheers,
Sajan.
 

Attachments

  • Chandoo-Jaine Davies-Sample_spreadsheet.xlsx
    18.5 KB · Views: 11
Hi Jaine,
I always forget about SUMIF...! So, here is another formula you could use (with the same results):

Put in cell T4 and copy down
=SUMPRODUCT(SUMIF(AwardCodes,M4:Q4,AwardRates))*$R4*52

Cheers,
Sajan.
 
:D Sajan...my hero! Thank you so much that is perfect! I like the latter one better as Ctrl+shift+enter does not seem to work on my pc for some reason but, as you say, the answer is the same.

Can you tell me what it means, in plain English, please though. Have you had to name cells AwardCodes and AwardRates, for example.

Thanks again :)
 
Hi Jaine,
Thanks for the feedback. Glad to help!

AwardCodes and AwardRates are named ranges in your worksheet that I setup to make referencing easier to read. You can just easily substitute them with the actual ranges. The key advantage to named references is that if you need to change the range of cells, you can make them without having to repair your formulas that reference them.

SUMIF function compares each value in AwardCodes to each value in M4:Q4, and adds up the values in AwardRates when there is a match. Normally, SUMIF expects one cell reference instead of M4:Q4.

Since we have provided a range of cells (M4:Q4), the SUMIF calculation is repeated for each of those cells.

The result from SUMIF is an array of sums (for each of the cells in M4:Q4). So we aggregate all of the result values using SUMPRODUCT.

The last part of the formula multiplies 52 (number of weeks in a year) with the number of years found in R4, with the result of the SUMPRODUCT. That gives you the total award amount.

If you wish to learn more about these functions (and more), there are many articles and posts on this site that offer more explanations and examples. (You can retrieve them using a search using the Search bar located on the upper right hand of this page.)

Cheers,
Sajan.
 
Brilliant! I actually understand that Sajan! Amazing ;)

Thank you so much everyone. The sheet is now working as I would like so, fingers crossed, the admin team will be able to use this easily until the DB is set up.

Thanks again x
 
Good day Janie

Me thinks that after all the coding that Sajan has done he will pay you a visit if you still go the Access route....:p
 
You must log in or register to reply here.
Back
Top