sumproduct/vlookup/match....?

S

syed_mushraf

Active Member
Joined
Oct 13, 2002
Messages
261
Dear experts I need your help. again I am stuck with this formula.
i have two data ranges as following.

Range-1
Item Week Site Data
21110 42 FCL 100
21110 43 UDL 50
21112 44 FCL 40
21113 42 FCL 200
21110 41 ASL 150
21112 43 UDL 303
21113 43 UDL 88


Range-2 Site Wk# Wk# Wk# Wk# Wk#
42 43 44 45 46
21114 FCL
21110 UDL
21112 ASL
21113 UDL
21112 FCL

My requirement is to fill data in the range-2 by considering 2 column given left in range-2. Plz help me how can I use SUMPRODUCT/VLOOKUP/MATCH/INDEX to get the desired result.
Rgds,
Musharraf
 

Excel Facts

How to show all formulas in Excel?
Press Ctrl+` to show all formulas. Press it again to toggle back to numbers. The grave accent is often under the tilde on US keyboards.
Sort by date Sort by votes
syed_mushraf said:
Dear experts I need your help. again I am stuck with this formula...
Click to expand...

I don't see any duplicates of <Item,Week,Site> in Range-1. It seems you just want to retrieve the value associated with a given triplet <Item,Week,Site>. not to sum. Right?
 
Upvote 0
syed_mushraf said:
D.Expert I am unable to get triplet result. Plz help.
Click to expand...
Book4
ABCDEFG
1Range-1(Source)
2ConcatItemWeekSiteData
321110FCL422111042FCL100
421110UDL432111043UDL50
521112FCL442111244FCL40
621113FCL422111342FCL200
721110ASL412111041ASL150
821112UDL432111243UDL303
921113UDL432111343UDL88
10
11
12Range-2(Destination)Wk#Wk#Wk#Wk#Wk#
13ItemSite4243444546
1421114FCL00000
1521110UDL050000
1621112ASL00000
1721113UDL088000
1821112FCL004000
Sheet1


Formulas...

A3, which is copied down:

=B3&CHAR(127)&D3&CHAR(127)&C3

C14, which is copied across the down:

=SUMIF($A$3:$A$9,$A14&CHAR(127)&$B14&CHAR(127)&C$13,$E$3:$E$9)

This formula behaves effectively as a retrieval formula if there aren't triplets with repeats, otherwise it will sum the figures.

A pure retrieval formula in C14 would be:

=INDEX($E$3:$E$9,MATCH($A14&CHAR(127)&$B14&CHAR(127)&C$13,$A$3:$A$9,0))

which does not suppress #N/A in case of failure.
 
Upvote 0
You must log in or register to reply here.

Similar threads

P
  • Solved
Compare multiple columns and list common values in separate column
2
Replies
10
Views
420
Preacherman771
P
A
  • Solved
index match using column headers
Replies
4
Views
234
AlanY
A
P
  • Solved
Update Equally Distributed Formula
2
Replies
19
Views
592
felixstraube
F
D
  • Question
List Generator with index match within date range
Replies
2
Views
443
Peter_SSs
Peter_SSs
R
  • Solved
vlookup, INDEX, or SUMPRODUCT???
2
Replies
10
Views
329
KRice
K

Forum statistics

Threads
1,214,787
Messages
6,121,558
Members
449,038
Latest member
Guest1337

Share this page

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