a twist on a VLOOKUP?

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I have a spreadsheet with numerous columns (about 40). I need to search the
spreadsheet for a particular code and then sum the values that would be 4
columns to the right of this code. The code could be anywhere in the
existing 40 columns and will be there many times. How do I achieve this?
 
I'm not sure I understand this, but see if this helps:

=SUMPRODUCT(--(A1:AN100=123)*OFFSET(A1:AN100,0,4))

This assumes your data is in the range A1 through AN100. Adjust to suit
your needs. It then finds each instance of the code (I used 123), and then
sums the value found 4 columns to the right of each of those instances.

Is that what you're looking for?

HTH,
Elkar
 
Sumif() will do what you want.

=SUMIF(Sheet3!$B$8:$B$15,Sheet1!B9,Sheet3!$F$8:$F$15)

Parameter 1 of 3 The range containing your lookup values
Parameter 2 of 3 The Lookup Value of Interest
Parameter 1 of 3 The Column to Sum

HTH
 
The limitation with the below formula is that it looks as though you are
having to tell it a specific column to sum. I will not know which column the
values are in. I only know that they are 4 columns to the right of the code
I am looking for. The code could be in any number of columns.

I do appreciate your help.
 
Sorry, I missed that portion of your requirements,,,
Looks like You got your answer all the same.

Hooray, hooray !!
 
This did not work for me. Perhaps if I can understand a few things I could
adjust it to work. Why SUMPRODUCT since I do not need the product of
anything? What does the * do? What does the -- do?
 
As Peo mentioned, the -- is not necessary. That was left over from my
original thought, which I forgot to remove. It really should have no effect
on the formula though.

The use of the SUMPRODUCT function creates an array.

Basically, think of it as each cell in your range is evaluated
independently, then the results are all added together at the end. So, in my
example, we start with cell A1. This is checked to see if it equals the code
123. If yes, it returns TRUE which has a value of 1. If no, it returns
FALSE which has a value of 0. The * symbol then multiplies the 1 or 0 by the
value found 4 cells to the right, in this case E1. Since anything multiplied
by 0 is 0, all false conditions are effectively eliminated from the final
sum. This then continues on for each cell in the range.

Since its still not working, perhaps a better explanation of what you want
along with some examples might help.

HTH,
Elkar
 
The formula that you so kindly provided seems to be exactly right, however I
get a #value! error. This is supposed to indicate that the array argument
does not have the same dimensions. Does that refer to the range? I did use
the same cell range in both sections of the formula.
 
The #VALUE! error could also occur if one of the numbers being summed is not
a number. Do you have text values that would be 4 columns to the right of
any of the codes you're looking up?
 
I take that back. Some of the cells in the range have text. However, none
of the cells four columns from the codes have text.
 
Back
Top