Databases
StackOverflow2013
Postsdbo
PO
 

Note that there are some explanatory texts on larger screens.

plurals
  1. PO
    primarykey
    data
    text
    <p>To make the values appear in the Sheet 2 table, use the following formula:</p> <pre><code>=SUMPRODUCT(--($A$2:$A$7=E2),--($B$2:$B$7=$F$1),$C$2:$C$7) </code></pre> <p><img src="https://i.stack.imgur.com/1dOK0.png" alt="enter image description here"></p> <p>This returns <code>0</code> for non-matches - you can either format the cells to display 0 how you want, or you can use the longer/uglier:</p> <pre><code>=IF(SUMPRODUCT(--($A$2:$A$7=E2),--($B$2:$B$7=$G$1),$C$2:$C$7)&lt;&gt;0,SUMPRODUCT(--($A$2:$A$7=E2),--($B$2:$B$7=$G$1),$C$2:$C$7),"-") </code></pre> <hr> <p>To calculate the rank on the first sheet based on the data from the second sheet:</p> <pre><code>=VLOOKUP(A2,$F$2:$H$5,MATCH(B2,$G$1:$H$1,0)+1,FALSE) </code></pre> <p>For sample purposes, this just put your sheet2 data in F1:H5. <img src="https://i.stack.imgur.com/rDgZw.png" alt="enter image description here"></p> <p>This looks for the corresponding keyword and then uses match to pick the right column. I named the columns <code>Z</code> and <code>Y</code>, but if you need <code>Domain</code> included that can be done as well. Note that this causes an error since there is no <code>E</code> defined in your second table - is that the case? If so, it can be adjusted to account for no matches as follows (assuming Excel 2007):</p> <pre><code>=IFERROR(VLOOKUP(A6,$F$2:$H$5,MATCH(B6,$G$1:$H$1,0)+1,FALSE),"Rank Not Found") </code></pre> <p><img src="https://i.stack.imgur.com/VGAt5.png" alt="enter image description here"></p>
    singulars
    1. This table or related slice is empty.
    1. POIndex/Match multiple columns in Excel
      singulars
      1. PTQuestion
    1. PTAnswer
    1. USRocketDonkey
    1. USRocketDonkey
    plurals
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. POIndex/Match multiple columns in Excel
      singulars
      1. PTQuestion
    1. This table or related slice is empty.
    1. VO
      singulars
      1. PO
      1. This table or related slice is empty.
      1. VTAcceptedByOriginator
    2. VO
      singulars
      1. PO
      1. This table or related slice is empty.
      1. VTUpMod
    3. VO
      singulars
      1. PO
      1. This table or related slice is empty.
      1. VTUpMod
    1. COI've tried both your formulas. Everything I'm getting is #REF ("or the error if I use your 2nd formula). I stepped through the formula and everything evaluates up until the VLOOKUP. At that point it fails. My knowledge of Excel isn't enough to know why. I did modify your formula a bit to make it fit my sheets. I'll also add that in your example, Columns G and H I want to be replaced with the formula. I simple put numbers in there to show what I want. I'm not sure if VLOOKUP works here (again, could be wrong) because the columns it's looking at is not sorted in any order and is words w/ spaces
      singulars
      1. PO
      1. USKurt
    2. COGotcha - so you want the formulas to be in columns G and H, basically saying "For keyword A, find the corresponding rank for domain Z"? The #REF is basically just saying that you are referring to an invalid reference, which happens easily when try to port a formula from one place to another :) Does it work if you set it up just like the example above? I'll amend the answer to handle your actual case.
      singulars
      1. PO
      1. USRocketDonkey
    3. COTo your question, that's exactly what I'm looking to do. It does work if I set it up like your example.
      singulars
      1. PO
      1. USKurt
 

Querying!

 
Guidance
A row detail

Detail views are divided into sections. All the information in the data section comes from columns in the selected row. The other sections display data from other, related rows.

Related data can be related in a to-one or a to-many fashion. Captions of data related in a to-many fashion link to a list view showing a filtered view of the table.

Try moving around until you find a non-empty to-many entry and click on the label to get to one. You can move back to the root by clicking on the database name in the header.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload