MS Excel: Multiple condition based matrix search -


I have a scenario to bring the cost against the number of links deployed by a vendor (a specific type) I have a VLookup + Has tried an index-match but it does not behave as I want it to be. The only index-match work for a single matrix, but it does not get the WRT media type i.e. fiber optics, DSL or DRS values.

Table 1 : This table contains the number of links and will be the output amount of each vendor.

For example, we have the 2 link of "100 Mbps" "Fiber Optic" provided by "Vendor 1".
We have a 18 link "seller 3" of "1 Mbps" "DSL"

Table 2 : This table contains the price of each link WRT vendor.

Example: "Seller 1" charge for 400 "100 Mbps" "Fiber Optic" link "Vendor 3" fee for <"512 kbps" "DRS" link 80

requirement : I should be calculated in relation to the value of amount in 1 of the table Relevant sellers for specific media and bandwidth.
"100 Mbps" provided by "Vendor 1" will be amount for 2 links of "fiber optics": 400 x 2 = 800

Indie for me S-Match is fine for "Vendor X Bandwidth" but unable to include "Media" in the selection criteria.

I do enter image details here

let me know If you have questions, then the formula is array formula ... CTRL + SHIFT + ENTER 

  = G4 * index ($ C $ 11: $ E $ 22, match ($ B4, IF ($ A $ 11: $ A $ 22 = $ A4), $ B $ 11: $ B $ 22), 0), Match (H $ 2, $ C $ 10: $ E $ 10,0)) < / Code>

drag each amount into the column and You are golden, I simplified the top array for the timeasek, but it uses INDEX + MATCH with two criteria. Notice I did not use the merge, you can not do this in the case unless you have additional work Do not want to and can not edit each thread.

If you do not like it, hide the columns.


Comments

Post a Comment

Popular posts from this blog

python - Overriding the save method in Django ModelForm -

I'm having trouble overriding a ModelForm save method. I get this error I'm: Exception type: TypeErier Exception Value: Save () found an unexpected keyword argument 'committed' My intentions include a form 3 fields Submit several values ​​for, then create an object for each combination of those areas, and to save each object file models.py class collarsult type (models.Model): id = model. AutoField (db_column = 'icontact_result_code_type_id', primary_key = true) callResult = models.ForeignKey ( 'CallResult', db_column = 'icontact_result_code_id') campaign = models.ForeignKey ( 'campaign', db_column = 'icampaign_id') CALLTYPE = models.ForeignKey ( 'CALLTYPE', db_column = 'icall_type_id') agent = models.BooleanField (db_column = 'bagent', default = true) teamLeader = models.BooleanField (db_column = 'bTeamLeader', default = true) active = models.BooleanField (db_column = Django.form...
Read more

html - CSS autoheight, but fit content to height of div -

I have a div in which there are three children- div in the left and middle div: float: left while true: float: true because it is my layout Actually will spoil, I've used ClearFix Hack: .cf {zoom: 1; }. Cf: First,. Cf: {content: ""; Display: Table; }. Cf: After {clear: both; } It still works, but I want to be the right div an indicator. So it should fill 100% of the parent unit of height. How can I complete this? PS is the full code: & Lt; / Div & gt; & Lt; Div class = "mobile_content" & gt; & Lt; H5 & gt; {{Data.titel}} & lt; / H5> & Lt; Table & gt; & Lt; TR & gt; & Lt; Th & gt; Komponist: & lt; / Th & gt; & Lt; TD & gt; {{Data.komponist}} & lt; / TD & gt; & Lt; / TR & gt; & Lt; TR & gt; & Lt; Th & gt; Instrument: & lt; / Th & gt; & Lt; TD & gt; {{Data.instrumente}} & lt; / TD & gt; & Lt; / TR & gt;...
Read more

qt - How to prevent QAudioInput from automatically boosting the master volume to 100%? -

I have been trying to use Qt5 multimedia to record audio with QAudioInput, however, when I see If QAudioInput is started, it increases the master volume of my sound device to 100%. How can I prevent QAudioInput from changing the master volume? My current development platform is Linux with PalsAdio (with flat audio disabled). How can I use QAudioInput : QAudioDeviceInfo device_info = QAudioDeviceInfo :: defaultInputDevice (); QAudio format format; Format.setSampleRate (44100); Format.setChannelCount (1); Format.setSampleSize (16); Format.setCodec ("Audio / PCM"); Format.setSampleType (QAudioFormat :: SignedInt); Format.setByteOrder (QAudioFormat :: LittleEndian); Std :: cout & lt; & Lt; Device_info.deviceName (). ToUtf8 (). ConstData () & lt; & Lt; Std :: endl; QAudioInput * default_device = new QAudioInput (device_info, format); QIODevice * default_io_device = default_device-> Start (); a QAudioInput.setVolume () method is not you I ...
Read more