OFFSET query

[steve80s]

Hi,

I have a formula that's working perfectly for me, which is this...

"=IF($C4=0,"",MIN(IF('Main Log'!$D$4:$D$2000=$B4,IF('Main Log'!$O$4:$O$2000,"",'Main Log'!$E$4:$E$2000))))

It finds the minimum value (earliest date) in my 'Main Log' sheet where the criteria are met.

However, in the next column, I want to show the contents of the cell 2 columns to the right of the earliest date as identified by that formula.

In other words, if I use the formula above in D5, it will correctly return the oldest Clinic Date matching the criteria I set up. Now I want D6 to show me the NHS. No for that clinic date (which is located 2 cells to the right of the Clinic Date).

Clinic Type	Clinic Date	Clinician	NHS No.
SACC	02/08/2022		765433
SACC	01/01/2022		1234567
Neuromotor	10/08/2022		12334234

I can't get this to work. Can anyone advise please?

Thanks in advance.

 

[Anthony47]

If you have the Date in D5 then you can use vlookup; ie in D6

=VLOOKUP(D5,'Main log'!B1:D20,3,0)

 

[steve80s]

If you have the Date in D5 then you can use vlookup; ie in D6

=VLOOKUP(D5,'Main log'!B1:D20,3,0)

Hi,

Thank you for replying.

However, the issue is, there are sometimes multiple clinics on the same date. This is returning the first instance of finding the date on the list. It also needs to take into account the criteria as given in my original D5 formula.

If you look at my original spreadsheet below it will probably make more sense. The actual location I'm trying to find a formula for is the Current Status sheet, L4. My original formula is on Current Status sheet, J4.

You can see both SACC and JACC have clinics on the same date (Main Log). Your formula is just pulling details of Ian and Pluto from the log for both.

Any other ideas please?

 

[Fluff]

Welcome to the MrExcel Message Board!

Cross-posting (posting the same question in more than one forum) is not against our rules, but the method of doing so is covered by #13 of the Forum Rules.

Be sure to follow & read the link at the end of the rule too!

Cross posted at: How to use OFFSET when the reference is a formula linked to another tab?
If you have posted the question at more places, please provide links to those as well.

If you do cross-post in the future and also provide links, then there shouldn’t be a problem.

 

[Anthony47]

Don't miss the Administrator message, above...

Please provide a usable sample of data that includes all the columns mentioned in the formula
With the current information I would guess that the second formula should be

=IF($C4=0,"",MIN(IF('Main Log'!$D$4:$D$2000=$B4,IF('Main Log'!$O$4:$O$2000,"",'Main Log'!$G$4:$E$2000))))

(two columns after E)

But at this stage my suggestion is:
-use your formula to get the "index" of the target row; the formula should be:

=IF($C4=0,"",MIN(IF('Main Log'!$D$4:$D$2000=$B4,IF('Main Log'!$O$4:$O$2000,"",ROW('Main Log'!$E$4:$E$2000)))))

-then use this Index to extract from the table in MainLog the columns that you need using INDEX; for example, if the index is in I4 and you want to get column E of MainLog:

=INDEX('Main Log'!$E$1:$E$2000,$I4)

This will save repeating the same tests in the final formulas, that is especially useful if you have to extract several information out of the same row

 

[Kaper]

See the answer I published in the thread (crossposted):

How to use OFFSET when the reference is a formula linked to another tab? [SOLVED]

Hi All, I have a formula that works perfectly... =MIN(IF('Main Log'!$D$4:$D$2000=$B4,IF('Main Log'!$O$4:$O$2000,"",'Main Log'!$E$4:$E$2000))) What this does is checks my 'Main log' to look for the instances where: - Appointment type matches what I've specified in B4 (in Column D) -The...

www.excelforum.com

The formula is (replace semicolons with commas if your locale uses commas as function argument separators):

=INDEX('Main Log'!$G$1:$G$2000;MIN(IF(('Main Log'!$D$4:$D$2000=$B4)*('Main Log'!$O$4:$O$2000="")*('Main Log'!$E$4:$E$2000=J4);ROW('Main Log'!$D$4:$D$2000);"")))

 

[steve80s]

That's perfect. Thank you Kaper!