Lookup ROW of information and return matching data points

**adamrueda** · 07-09-2019, 03:34 PM

Hi all -

I am looking to concoct a function that will return what I need when I need it. The problem is this:

Firstly, I need to lookup the ROW of information I will be pulling from. The left-most column is labeled with a LOAN NUMBER that I want to enter and lookup corresponding data that is within the rows that match that LOAN # (to the right of it).

Of these ROWS that are labeled with this LOAN NUMBER (there will be 3-10), I need to choose the one ROW that has the most recently passed date from today.

Once locating this row (yay!), I just need to pull one of the other data points in this row as my return.

Thank you so much for anyone willing to help me on this,
Adam

**jeffreybrown** · 07-09-2019, 03:44 PM

Hi Adam & Welcome to the forum,

How about attaching a sample workbook!

Attach a sample workbook (not a picture or pasted copy). 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 then scroll down to Manage Attachments to open the upload window.

**adamrueda** · 07-09-2019, 04:02 PM

Ok! Here you go! Thank you so much!

**FlameRetired** · 07-09-2019, 06:10 PM

Please try

Formula:

Please Login or Register  to view this content.

**adamrueda** · 07-09-2019, 06:28 PM

How do I enter less than or equal to signs? What cell are you referring to when you put "C1"?

**adamrueda** · 07-09-2019, 06:34 PM

Ignore the first question but I don't know what you are referring to when you use C1

**protonLeah** · 07-09-2019, 06:55 PM

Replace "C1" with TODAY() or the cell with today's date

**FlameRetired** · 07-09-2019, 07:15 PM

Originally Posted by adamrueda

... but I don't know what you are referring to when you use C1

My bad. I neglected to explain that part. My apologies. Ben explained it.

Thanks, Ben for the backup.

**adamrueda** · 07-10-2019, 08:56 AM

Im struggling with this. I made C1 = TODAY and am getting a NUMBER error. Is there a specific tab this should be on?

**jeffreybrown** · 07-10-2019, 09:00 AM

Change the B3 references in the formula to B2.

**adamrueda** · 07-10-2019, 09:04 AM

Thank you!! Now comes the step of transposing it to my actual data but I will let you know how it goes

**jeffreybrown** · 07-10-2019, 09:09 AM

You are very welcome. We are all happy to help. Thanks for the feedback.

**adamrueda** · 07-10-2019, 09:28 AM

I translated everything diligently into my own data but am returning "0" ? Any tips? Does it have to do with the 1s and 0s toward the ends of the functions if I am working with a physically larger table? Adam

**adamrueda** · 07-10-2019, 09:32 AM

Should my first array for the Index function be the entire table I am working with or just the column of information I am comparing entry with?

**adamrueda** · 07-10-2019, 09:42 AM

Im trying to do the orange cell on Vanilla Calc. I put in what you have shown me so far.

**adamrueda** · 07-10-2019, 09:43 AM

Here is the real data

**FlameRetired** · 07-10-2019, 11:53 AM

This part is a circular reference. From what range do you want the return(s)?

=INDEX('Vanilla Calculator'!$B$3:$B$10,MATCH(AGGREGATE(14,6,'Data for Vanilla Loans'!$C$3:$C$10/((B2='Data for Vanilla Loans'!$A$3:$A$10)*('Data for Vanilla Loans'!$C$3:$C$10<=C1)),1),INDEX('Data for Vanilla Loans'!$C$3:$C$10/((B2='Data for Vanilla Loans'!$A$3:$A$10)*('Data for Vanilla Loans'!$C$3:$C$10<=C1)),0),0))

**adamrueda** · 07-10-2019, 01:14 PM

Prepayment status

**JeteMc** · 07-13-2019, 11:32 PM

Try:

Formula:

Please Login or Register  to view this content.

Let us know if you have any questions.

Lookup ROW of information and return matching data points

LinkBack

Thread Tools

Rate This Thread

Display

Lookup ROW of information and return matching data points

Re: Complex INDEX / MATCH / IF conundrum

Re: Lookup ROW of information and return matching data points

Re: Lookup ROW of information and return matching data points

Re: Lookup ROW of information and return matching data points

Re: Lookup ROW of information and return matching data points

Re: Lookup ROW of information and return matching data points

Re: Lookup ROW of information and return matching data points

Re: Lookup ROW of information and return matching data points

Re: Lookup ROW of information and return matching data points

Re: Lookup ROW of information and return matching data points

Re: Lookup ROW of information and return matching data points

Re: Lookup ROW of information and return matching data points

Re: Lookup ROW of information and return matching data points

Re: Lookup ROW of information and return matching data points

Re: Lookup ROW of information and return matching data points

Re: Lookup ROW of information and return matching data points

Re: Lookup ROW of information and return matching data points

Re: Lookup ROW of information and return matching data points

Thread Information

Users Browsing this Thread

Similar Threads

[SOLVED] Partial Url Lookup and Return Matching Value from List

Lookup start - end date, Return information

Want to lookup multiple matching criteria and return multiple data in columns

[SOLVED] Lookup cell value and return matching all data from another workbook.

[SOLVED] Lookup matching Value and return name

Trying to do a lookup + Return of information

Lookup and return information from several worksheets

Tags for this Thread

Bookmarks

Bookmarks

Posting Permissions