Find Last Entry Between 2 Columns

Hi,

I'm seeking to look up last entry by first finding criteria in another column as follows:

Using the following in single column works well:

=LOOKUP(9.99E+307,C118:C153)

Now, I'm looking to add in column or cells: e118:e153 & find criteria, "797577",

How can this function or another be used?

Thanks

Hi Group,

I managed to find something that partially works with the following:

=INDEX($C$118:$C$153,MATCH("*797577*",$D$118:$D$153,0))

However, instead of seeking or matching first criteria found, I rather find the last. How can this be arranged?

Thanks

Hi,

Does anyone have a suggestion or two?

Seeking to find last entry criteria - In this case - last time "797577" is found & the corresponding last entry within same row.

Thanks

Try substituting MATCH("*797577*",$D$118:$D$153,0) with MATCH("*797577*",$D$118:$D$153,1)

That failing could you upload a sample workbook with representative data? I'm not clear how much is

text and how much might be numeric data in your description.

Try this

=LOOKUP(2,1/(E118:E153=797577),C118:C153)

Row\Col	C	D	E
118	25	65	797577
119	15		654654
120	45		456987
121	25		797577
122	45		797577
123	65		797577
124	456		456645
125	54		456645
126	52		456645
127	12		456645
128	3211		654654

Hi Alkey & Others,

Thanks for this great example! It looks like it should work but for whatever reasons, I'm getting a "#N/A"

Will a Vlookup type of function work given that my result is on the left of my criteria & I'm seeking the last result instead of first?

Please attach a sample workbook. Make sure there is just enough data to demonstrate your need. Include a BEFORE sheet and an AFTER sheet in the workbook if needed to show the process you're trying to complete or automate. Make sure your desired results are shown, mock them up manually if necessary.

Remember to desensitize the data.

Click on GO ADVANCED and use the paperclip icon to open the upload window.

View Pic

Hi Alkey & Others

I've been a bit busy & finally got an opportunity to get back into this forum.

As requested, I attached an example file. As you will see I have 2 cell blocks highlighted in yellow & green, with one of the following functions:

Cell, D44
=INDEX($C$6:$C$41,MATCH("*797577*",$D$6:$D$41,1))

Cell, F44
=LOOKUP(2,1/($D$6:$D$41="*797577*"),$C$6:$C$41)

Cell, D45
=LOOKUP(2,1/(D6:D41=D40),C6:C41)

First 2 aren't working for me & the last is only giving the last result from last row, as it should

Per date, seeking to have last entry, which in this example, I would be expecting to receive the following result:

Find "797577" & get result, $23015.95 from cell, C12

How to get working?

Thanks

Since I can't even find number 797577 on the spreadsheet nor the amount $23015.95 in cell C12 which is blank, I can only assume that maybe the file you provided is a wrong one.

Hi Alkey & Others,

Sorry - Not sure what happened, but it the wrong file got attached. Please see attached file.

Thanks

Try this array formula:

Please Login or Register  to view this content.

...confirmed by pressing CTRL+SHIFT+ENTER to activate the array, not just ENTER. You will know the array is active when you see curly braces { } appear around your formula. If you do not CTRL+SHIFT+ENTER you will get an error or a clearly incorrect answer. Press F2 on that cell and try again.

Drag down

Try this non-array formula in G5:

Formula:

Please Login or Register  to view this content.

Adjust ranges according to your need.

Row\Col	B	C	D	E	F	G
3		Find Last Entry Between 2 Columns
4
5	Date Due	Monthly Loan Balance	Loans	Minimum Due		$23,015.95
6	01/16/15	$22,920.91	Bal. 1 - 797577	$293.19
7		$22,400.37	Bal. 2 - 798581	$291.34
8		$45,321.28	Total	$584.53
9	02/12/15	$23,272.78	Bal. 1 - 797577	$274.76
10		$22,554.87	Bal. 2 - 798581	$260.98
11		$45,827.65	Total	$535.74
12	03/12/15	$23,015.95	Bal. 1 - 797577	$549.52
13		$22,296.72	Bal. 2 - 798581	$521.96
14		$45,312.67	Total	$1,071.48
15	04/12/15		Bal. 1 - 797577	$246.04
16			Bal. 2 - 798581	$239.15
17		$0.00	Total	$485.19

Hi bebo021999 & FlameRetired,

Thank you for the suggestions.

The "=LOOKUP(2,1/($C$6:$C$41<>0)*(SEARCH("*797577*",$D$6:$D$41)),$C$6:$C$41)" function works great & provides the results that I am seeking.