Hello guys!
I'm using the below vlookup to combine values across various sheet sheet (consolidate budget), it's returning #value! error.
I understand, this due to some matching value does not exists in other sheet, but that is ok. I
Appreciate, if someone could render your usual support go get rid of this.
IFERROR(VLOOKUP(B28,SPIDER,3,0),"")+IFERROR(VLOOKUP(B28,MCLUB,3,0),"")+IFERROR(VLOOKUP(B28,QNCC,4,0),"")
Last edited by Felix212; 10-29-2017 at 06:04 AM.
But it's not OK, otherwise you would not get a #VALUE! error.
1. Check that the VLOOKUP column references are correct - two reference column 3 and the third column 4 of the array - is this correct?
2. Failing the above, attach the workbook here - there will be some mismatch between lookup values and the values in the lookup arrays.
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.
Try this ...
=SUM(IFERROR(VLOOKUP(B28,SPIDER,3,0),""),IFERROR(VLOOKUP(B28,MCLUB,3,0),""),IFERROR(VLOOKUP(B28,QNCC,4,0),""))
try
IFERROR(VLOOKUP(B28,SPIDER,3,0)+VLOOKUP(B28,MCLUB,3,0)+VLOOKUP(B28,QNCC,4,0),"")
you only need to check if any of them have an error - not each individually
Otherwise you are adding a "" blank to a number
thats assuming if anyone fails you dont want to still add the other two numbers together
Last edited by etaf; 10-28-2017 at 05:31 AM.
Wayne
if my assistance has helped, and only if you wish to , there is an "* Add Reputation" on the left hand side - you can add to my reputation here
If you have a solution to your thread - Please mark your thread solved do the following: >
Select Thread Tools-> Mark thread as Solved. To undo, select Thread Tools-> Mark thread as Unsolved.
Hey Etaf!
I have three sheets, in each sheet left first column has the look up value.
Concern is, some of the look up value are same down in the column. (i.e) i have similar look up values down in the same column (doubled or duplicated, but i need them)
I guess, this is not helping to capture, when i consolidate in a sheet.
Any help, how to perform this?
Will you please attach a sample Excel workbook? Nobody wants to have to recreate your data from scratch.
1. Make sure that your sample data are REPRESENTATIVE of your real data. The use of unrepresentative data is very frustrating and can lead to long delays in reaching a solution.
2. Make sure that your desired solution is also shown (mock up the results manually).
3. Make sure that all confidential data is removed or replaced with dummy data first (e.g. names, addresses, E-mails, etc.).
4. Try to avoid using merged cells as they cause lots of problems.
Unfortunately the attachment icon doesn't work at the moment, so to attach an Excel file you have to do the following: just before posting, scroll down to Go Advanced and then scroll down to Manage Attachments. Now follow the instructions at the top of that screen.
Please pay particular attention to point 2 (above): without an idea of your intended outcomes, it is often very difficult to offer appropriate advice.
Hi Ali!
Many thanks once again!
Sorry about that, please find attached my work out sheet.
Kind regards
So on the consolidation sheet, in E6 copied down:
=IFERROR(VLOOKUP(B6,sp,3,0),0)+IFERROR(VLOOKUP(B6,mc,3,0),0)+IFERROR(VLOOKUP(B6,cq,4,0),0)
Using the blank ("") in the IFERROR statement was causing the #VALUE! error.
If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED. Thanks.
Dear Ali -
Thanks!
my question is not about value error
It is about, the look up value in the last two sheets gets repeated. This leads me to a challenge, summing up in consolidation, (look up is not picking up the figures that are in similar look up value)
In E7 copied down:
=IFERROR(SUMIF('1'!$A$2:$A$29,'Conlidated WIP'!$B7,'1'!D$2:D$29),0)+IFERROR(SUMIF('2'!$A$3:$A$40,'Conlidated WIP'!$B7,'2'!C$3:C$40),0)+IFERROR(SUMIF('3'!$A$3:$A$40,'Conlidated WIP'!$B7,'3'!C$3:C$40),0)
VLOOKUP will only find the first match.
If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED. Thanks.
If you are consistent in the layout of the 3 sheets (same starting row, same data columns i.e add column C into Sheets 2 and 3)
try
=SUMPRODUCT(SUMIFS(INDIRECT("'"&Sheets&"'!D2:D100"),INDIRECT("'"&Sheets&"'!A2:A100"),B6))
where "Sheets" is named range of your tab names
Many thanks Ali!
Appreciate your patience and support in solving this!
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks