Excel formula find address of the last cell in a row/column of values/text which contains same value/text

[FATMIR]

a have this case:

	A	B
1	0150358	100
2	0150358	200
3	0150358	250
4	0150358	30
5	0160001	80
6	0160001	120
7	0160001	90

<tbody>
</tbody>

I have to find the address of the cell in B column which is related with the last cell in a row (A) which contains the cells with the same text/value.
In the table case above the outcome should be B4, if the condition is to search for "0150358", or B7, if the condition will be to search for "0160001".
Thank you in advance

 

[Marcelo Branco]

Welcome to Mr Excel forum

Try

	A​	B​	C​	D​	E​
1​	150358​	100​		Criteria​	Result​
2​	150358​	200​		150358​	B4​
3​	150358​	250​		160001​	B7​
4​	150358​	30​
5​	160001​	80​
6​	160001​	120​
7​	160001​	90​
8​

Formula in E2 copied down
="B"&LOOKUP(2,1/(A$1:A$7=D2),ROW($A$1:$A$7))

M.

 

[FATMIR]

Thank you very much Marcelo.
It works perfectly.

 

[Marcelo Branco]

You are welcome. Thanks for the feedback.

M.

 

[FATMIR]

Now i'm trying to incorporate the founded address with the formula: ="B"&LOOKUP(2,1/(A$1:A$7=D2),ROW($A$1:$A$7)) which result was B4, in the formula for calculation of NPV :which should be =NPV(C1,B1:B4).
I'm trying to find by formula this B4:
=NPV(C1,B1"B"&LOOKUP(2,1/(A$1:A$7=D2),ROW($A$1:$A$7))), but it is not working. the formula does not recognise the outcome address from the lookup formula, to be used in the row selection : B1:B4.
Maybe i'm completely out way. I have to use NPV formula with the condition of the selection of the cash flow, according to the condition 150358 last in the row.

 

[Marcelo Branco]

You need something like this

	A​	B​	C​	D​	E​	F​	G​	H​
1​	Code​	Value​		Rate​	Criteria​	FirstRow​	LastRow​	NPV​
2​	150358​	100​		10%​	150358​	2​	5​	$464,52​
3​	150358​	200​			160001​	6​	8​	$239,52​
4​	150358​	250​
5​	150358​	30​
6​	160001​	80​
7​	160001​	120​
8​	160001​	90​
9​

Rate in D2

Criteria in column E

Formula in F2 copied down
=MATCH(E2,A:A,0)

Formula in G2 copied down (assumes the codes are grouped)
=F2+COUNTIF(A:A,E2)-1

Formula in H2 copied down
=NPV(D$2,INDEX(B:B,F2):INDEX(B:B,G2))

Hope this helps

M.

 

[FATMIR]

Thank you very much Marcelo.
This is what i need.
very kind from your side.