Convert VLOOKUP to SUMPRODUCT

stuartmacdonald

New Member
Joined
May 26, 2009
Messages
48
I have a VLOOKUP that works with some workbooks but not others. I'm wondering if SUMPRODUCT would work better, but despite numerous configurations, I can't get it to work...
Not sure where to put --

Any ideas on how to convert this to SUMPRODUCT?

=IFERROR(VLOOKUP(B18,'https://cles-my.sharepoint.com/personal/stuartmacdonald_cles_org_uk/Documents/Order book/Timesheets/[Timesheet_NM.xlsx]Projects_NM'!$A:$S,19,FALSE),0)
 

Excel Facts

What is the shortcut key for Format Selection?
Ctrl+1 (the number one) will open the Format dialog for whatever is selected.
B18 is text, which is drawn together from 2 cells =CONCATENATE([@[Contract_no]]," ",[@Contract])

A typical result would be
CS-17-04 CfAB

<tbody>
</tbody>


I'm trying to match project codes from a master workbook with individual timesheets to sum total time spent on a project. The lookup goes to the timesheet, matches the project code and returns the value is column S - the 19th to the right....

It works fine with some sheets, but not others. All are structured exactly the same and I have checked....
 
Last edited:
Upvote 0
Try to check whether a failing look up value is present in column A of 'https://cles-my.sharepoint.com/personal/stuartmacdonald_cles_org_uk/Documents/Order book/Timesheets/[Timesheet_NM.xlsx]Projects_NM'.


=failing look up value = the presumably equal value in column A
 
Upvote 0
Column A in the timesheet is generated by a PowerQuery which draws it from Column B in the master - so they must be identical..
 
Upvote 0
I'm not sure what you mean

See post #4 .

Suppose I have a value that I want to lookup in X2.

This value must be looked for A2:B10. Suppose also that the VLOOKUP fails to return the expect result, while X2 occurs at A7.

The equality test we can carry out is:

=X2=A7

If we don't get TRUE from the foregoing test, we can conclude that something is wrong with X2 or with A7 or both X2 and A7, and try to establish what that is.
 
Upvote 0
The test on two identical cells is true, however, the VLOOKUP does not return the value. Even without the IFERROR formula, it is still returning a zero value....

=B22=[Timesheet_NM.xlsx]Projects_NM!$A$21

= TRUE


I have 10 workbooks linked in this way, the VLOOKUP on 4 of them does not work, while on 6 of them it does - I can't find anything different between them....
 
Upvote 0
The test on two identical cells is true, however, the VLOOKUP does not return the value. Even without the IFERROR formula, it is still returning a zero value....

=B22=[Timesheet_NM.xlsx]Projects_NM!$A$21

= TRUE


I have 10 workbooks linked in this way, the VLOOKUP on 4 of them does not work, while on 6 of them it does - I can't find anything different between them....

What result does VLOOKUP return -- #N/A?

Let's do a bit more
 
Upvote 0

Forum statistics

Threads
1,213,520
Messages
6,114,099
Members
448,548
Latest member
harryls

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top