software

Is it possible to use the Sumproduct formula to search for a particular word
and grab the amount in the next column, but 1 row down? For example, I would
search for region 1, but want to grab the figure in the total loans column
next to Consumer.

Region Loan Type Total Loans
--------- ------------ --------------
Region 1 Commercial 150000
Consumer 75000
Region 2 Commercial 90000
Consumer 145000

If this is possible, how would the formula be written? Thank you so much!

MATCH returns a row position.
SUMPRODUCT doesn't.

=INDEX(C2:C5,MATCH(quot;Region 1quot;,A2:A5,0) 1)

HTH
--
AP

quot;Jasminequot; gt; a écrit dans le message de
news: ...
gt; Is it possible to use the Sumproduct formula to search for a particular
gt; word
gt; and grab the amount in the next column, but 1 row down? For example, I
gt; would
gt; search for region 1, but want to grab the figure in the total loans column
gt; next to Consumer.
gt;
gt; Region Loan Type Total Loans
gt; --------- ------------ --------------
gt; Region 1 Commercial 150000
gt; Consumer 75000
gt; Region 2 Commercial 90000
gt; Consumer 145000
gt;
gt; If this is possible, how would the formula be written? Thank you so much!
It can be done with VLook up. I have used it in the past and it worked but
don't recall any longer. One thing I know is you will need to make sure that
your left most column will have to have the exact same text in it in order to
do the look up. Sorry I don't have more info.
--
Toshibaquot;Jasminequot; wrote:

gt; Is it possible to use the Sumproduct formula to search for a particular word
gt; and grab the amount in the next column, but 1 row down? For example, I would
gt; search for region 1, but want to grab the figure in the total loans column
gt; next to Consumer.
gt;
gt; Region Loan Type Total Loans
gt; --------- ------------ --------------
gt; Region 1 Commercial 150000
gt; Consumer 75000
gt; Region 2 Commercial 90000
gt; Consumer 145000
gt;
gt; If this is possible, how would the formula be written? Thank you so much!


Try:

=Index(B1:B10,Match(X1,A1:A10,0) 1)

where B1:B10 is the range containing your return values
A1:A10 contain the lookup matches

X1 is the value to match (i.e. Region 1).--
Vito
------------------------------------------------------------------------
Vito's Profile: www.excelforum.com/member.php...oamp;userid=29182
View this thread: www.excelforum.com/showthread...hreadid=539921Try something like this:

With your sample data in A1:C6

D1: =SUMIF(A1:A6,quot;Region 1quot;,C2:C7)

Notice the second reference is offset one row from the first reference.

Is that something you can work with?

***********
Regards,
Ron

XL2002, WinXP-Proquot;Jasminequot; wrote:

gt; Is it possible to use the Sumproduct formula to search for a particular word
gt; and grab the amount in the next column, but 1 row down? For example, I would
gt; search for region 1, but want to grab the figure in the total loans column
gt; next to Consumer.
gt;
gt; Region Loan Type Total Loans
gt; --------- ------------ --------------
gt; Region 1 Commercial 150000
gt; Consumer 75000
gt; Region 2 Commercial 90000
gt; Consumer 145000
gt;
gt; If this is possible, how would the formula be written? Thank you so much!

Perhaps this:
Same concept as my 1st post (ranges offset by one row), but using LOOKUP

=LOOKUP(quot;REGION 1quot;,A1:A6,C2:C7)

Does that help?
***********
Regards,
Ron

XL2002, WinXP-Proquot;Ron Coderrequot; wrote:

gt; Try something like this:
gt;
gt; With your sample data in A1:C6
gt;
gt; D1: =SUMIF(A1:A6,quot;Region 1quot;,C2:C7)
gt;
gt; Notice the second reference is offset one row from the first reference.
gt;
gt; Is that something you can work with?
gt;
gt; ***********
gt; Regards,
gt; Ron
gt;
gt; XL2002, WinXP-Pro
gt;
gt;
gt; quot;Jasminequot; wrote:
gt;
gt; gt; Is it possible to use the Sumproduct formula to search for a particular word
gt; gt; and grab the amount in the next column, but 1 row down? For example, I would
gt; gt; search for region 1, but want to grab the figure in the total loans column
gt; gt; next to Consumer.
gt; gt;
gt; gt; Region Loan Type Total Loans
gt; gt; --------- ------------ --------------
gt; gt; Region 1 Commercial 150000
gt; gt; Consumer 75000
gt; gt; Region 2 Commercial 90000
gt; gt; Consumer 145000
gt; gt;
gt; gt; If this is possible, how would the formula be written? Thank you so much!

I am getting a #N/A in the cell. Here is my formula.

=INDEX('C:\Trend Cards\Past Due Reports\[April Past
Due.xls]Sheet1'!C3:C10,MATCH(quot;LowCountry - Murrayquot;,A3:A10,0) 2)

quot;Vitoquot; wrote:

gt;
gt; Try:
gt;
gt; =Index(B1:B10,Match(X1,A1:A10,0) 1)
gt;
gt; where B1:B10 is the range containing your return values
gt; A1:A10 contain the lookup matches
gt;
gt; X1 is the value to match (i.e. Region 1).
gt;
gt;
gt; --
gt; Vito
gt; ------------------------------------------------------------------------
gt; Vito's Profile: www.excelforum.com/member.php...oamp;userid=29182
gt; View this thread: www.excelforum.com/showthread...hreadid=539921
gt;
gt;

Got it figured out. I needed to reference the worksheet I was pulling it from
in the Match part of the formula. Thank you for the help!

quot;Vitoquot; wrote:

gt;
gt; Try:
gt;
gt; =Index(B1:B10,Match(X1,A1:A10,0) 1)
gt;
gt; where B1:B10 is the range containing your return values
gt; A1:A10 contain the lookup matches
gt;
gt; X1 is the value to match (i.e. Region 1).
gt;
gt;
gt; --
gt; Vito
gt; ------------------------------------------------------------------------
gt; Vito's Profile: www.excelforum.com/member.php...oamp;userid=29182
gt; View this thread: www.excelforum.com/showthread...hreadid=539921
gt;
gt;


I think you need to reference the other workbook in the Match() part of
the formula too:

=INDEX('C:\Trend Cards\Past Due Reports\[April Past
Due.xls]Sheet1'!C3:C10,MATCH(quot;LowCountry - Murrayquot;,'C:\Trend Cards\Past
Due Reports\[April Past
Due.xls]Sheet1'!A3:A10,0) 2)

EDIT:

I see you have figured it out at the same time as I posted the same
solution. Great! and you're welcome--
Vito
------------------------------------------------------------------------
Vito's Profile: www.excelforum.com/member.php...oamp;userid=29182
View this thread: www.excelforum.com/showthread...hreadid=539921
Jasmine,

Using your sample data in A1:C5,

=SUMPRODUCT((A1:A5=quot;Region 1quot;)*(OFFSET(C1:C5,1,0)))

This returned 75000 for Region 1 and 145000 for Region 2.

Does that help?

Steve--
SteveG
------------------------------------------------------------------------
SteveG's Profile: www.excelforum.com/member.php...foamp;userid=7571
View this thread: www.excelforum.com/showthread...hreadid=539921