index/match formula to return variable/changing results from different sheet

[Owen1976]

Hi I've been using this formula to return data from a cell in another sheet, ATK1 is a cell with a date in it but when the cell I'm trying match has no data it is returning the date from ATK1, is there a way to return a blank or zero?

=INDEX(EXAM!$AB$2:$CL$13,MATCH(ATK1,EXAM!$AB$2:$CL$2,0),1)

Thanks
Ian

[cubangt]

You could wrap your formula with a IF function to check if that data exists and if it does, then run your formula, if it doesnt then dont

Something like this may help

Please Login or Register  to view this content.

[Owen1976]

Hi cubangt,

Still returns the date i'm afraid??

[cubangt]

would you be able to provide a sample file?

just a few rows in each sheet that is being used for the formula.

[Owen1976]

How do I upload files on here? thanks

[The_Snook]

Read the yellow banner at the top of the page.

[Owen1976]

Hi cubangt,

Think I've done it?

Thanks guys

as it stands it does what i need, but when i change the date on the "EXAM" tab in cell AB2 to today()+1 for testing it i get the date returned in the "7 day plan" tab ATK4 cell? when i would like it to be blank!

[cubangt]

So looking at the column AC in Exam, there is only 1 value under 5/1/2021
So is your expected result in 7 Day Plan ATK4 = 350233 and then ATL and ATM would be blank?

[cubangt]

Try swapping the return value in the IF like so

Please Login or Register  to view this content.

[Owen1976]

Hi cubangt,

Yes that is exactly as I would like it to be, I've tried your last formula and its returning a blank when I need it to display 350233?

If there are 3 entries under a date in the EXAM tab I'd like them to be shown in the corresponding date column in the 7 day plan tab in the 3 cells ATK4, ATL4, ATM4.

If there are only 2 entries then id like just 2 results and a blank, if that makes sense?

Thanks
Ian

[cubangt]

it makes sense, but not sure index match would be the way to go about it..
May have to use another method to get all the results with that date,

Take a look at this link and see if this would help you get to what you need..
https://www.xelplus.com/return-multi...lues-in-excel/

If i can think of another way to do it, then ill post back.

[cubangt]

Will it always be 3 returned values max? or could there be more?
I asked because under each date there are 11 cells. Then on the 7 day plan sheet you have 6 yellow cells.. so just trying to see if it will always be a set number of potential values

[AliGW]

Administrative Note:

We would very much like to help you with your query, however the thread title does not really convey what your request is about. Tell us what you are trying to do, not how you think it should be done.

Please take a moment to amend your thread title. Make sure that the title properly explains your request. Your title should be explicit and not be generic (this includes function names used without an indication of what you are trying to achieve).

Please see Forum Rule #1 about proper thread titles and adjust accordingly. To edit the thread title, open the original post to edit and then click on Go Advanced (bottom right) to access the area where you can edit your title.

(Note: this change is not optional. No help to be offered until this moderation request has been fulfilled.)

[Owen1976]

Hi cubangt, 6 would be the max I’d ever need returned, thanks!

Will it always be 3 returned values max? or could there be more?
I asked because under each date there are 11 cells. Then on the 7 day plan sheet you have 6 yellow cells.. so just trying to see if it will always be a set number of potential values

[JohnTopley]

Try in ATQ

=IFERROR((INDEX(EXAM!$AB$2:$CL$13,COLUMNS($A:D),MATCH($ATQ1,EXAM!$AB$2:$CL$2,0))),"")

format cells as 0;-0;;@ to hide zeros returned from INDEX

[cubangt]

@owen1976 try John's solution and see if that works for you

I worked on a solution today and i got good results, but its alot more involved than his..

But i have uploaded my file as well..