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("Region 1",A2:A5,0)+1)

HTH
--
AP

"Jasmine" a écrit dans le message de
news: ...
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!

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.
--
Toshiba


"Jasmine" wrote:

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!

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: http://www.excelforum.com/member.php...o&userid=29182
View this thread: http://www.excelforum.com/showthread...hreadid=539921

Try something like this:

With your sample data in A1:C6

D1: =SUMIF(A1:A6,"Region 1",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-Pro


"Jasmine" wrote:

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!

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

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

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

XL2002, WinXP-Pro


"Ron Coderre" wrote:

Try something like this:

With your sample data in A1:C6

D1: =SUMIF(A1:A6,"Region 1",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-Pro


"Jasmine" wrote:

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!

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("LowCountry - Murray",A3:A10,0)+2)

"Vito" wrote:


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: http://www.excelforum.com/member.php...o&userid=29182
View this thread: http://www.excelforum.com/showthread...hreadid=539921

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!

"Vito" wrote:


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: http://www.excelforum.com/member.php...o&userid=29182
View this thread: http://www.excelforum.com/showthread...hreadid=539921

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("LowCountry - Murray",'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: http://www.excelforum.com/member.php...o&userid=29182
View this thread: http://www.excelforum.com/showthread...hreadid=539921

Jasmine,

Using your sample data in A1:C5,

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

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

Does that help?

Steve


--
SteveG
------------------------------------------------------------------------
SteveG's Profile: http://www.excelforum.com/member.php...fo&userid=7571
View this thread: http://www.excelforum.com/showthread...hreadid=539921