Match Function

[TkdKidSnake]

Hi all,

I am looking at potentially using the match function using two variables on one tab to two variables on a 2nd tab that will then report a value on the form a specific column in the 2nd tab to the 1st. I have tried to several variations but as of yet I am struggling to get anything that works hence now looking at the match function.

1st Tab: ECIL - Consolidated
Formula will be on tab "ECIL - Consolidated" looking at column A and Column D and then these need to match the following

2nd Tab: Schedule In Report
What is in column D on the 1st tab is in column E on this tab and what is in column D on the 1st tab is in column G of this tab.
Upon finding a match I would like it to report what is in column L

If anyone can help it would be greatly appreciated.

Thanks in advance

 

[Fluff]

What version of Excel are you using?

I suggest that you update your Account details (or click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)

What is in column D on the 1st tab is in column E on this tab and what is in column D on the 1st tab is in column G of this tab.

Me thinks one of these should be col A?

 

[TkdKidSnake]

What version of Excel are you using?

I suggest that you update your Account details (or click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)

Me thinks one of these should be col A?

Hi,

Its Office 365 and thank you I didnt know this was here now much appreciated.

I have now moved the what was in column D to column A, i'm still not sure how the function works or what it needs to look like so if you can advise it would be appreciated.

Thank you.

 

[Fluff]

Its Office 365

Thanks for that & updating your profile.
I'm still a bit confused on which columns are which.
Do we need to look for col A in col E & col D in col G & return col L?

 

[TkdKidSnake]

Thanks for that & updating your profile.
I'm still a bit confused on which columns are which.
Do we need to look for col A in col E & col D in col G & return col L?

We are comparing the data on the 1st tab columns A & D and if there is a match on the 2nd tab columns A & G and if both lots of date match then report what is in column L on the 2nd tab

1st Tab is called: ECIL - Consolidated
2nd Tab is called: Schedule In Report

 

[Fluff]

Ok, how about

=INDEX('Schedule In Report'!$L$2:$L$100,MATCH(A2&"|"&D2,'Schedule In Report'!$A$2:$A$100&"|"&'Schedule In Report'!$G$2:$G$100,0))

 

[TkdKidSnake]

=INDEX('Schedule In Report'!$L$2:$L$100,MATCH(A2&"|"&D2,'Schedule In Report'!$A$2:$A$100&"|"&'Schedule In Report'!$G$2:$G$100,0))

This seems to work a treat thank you, do you know if there is a way of return a blank rather than a zero if the cells blank?

 

[Fluff]

How about

=LET(Return,INDEX('Schedule In Report'!$L$2:$L$100,MATCH(A2&"|"&D2,'Schedule In Report'!$A$2:$A$100&"|"&'Schedule In Report'!$G$2:$G$100,0)),IF(Return="","",Return))

 

[TkdKidSnake]

How about

=LET(Return,INDEX('Schedule In Report'!$L$2:$L$100,MATCH(A2&"|"&D2,'Schedule In Report'!$A$2:$A$100&"|"&'Schedule In Report'!$G$2:$G$100,0)),IF(Return="","",Return))

Awesome brilliant thank you, I honestly didn't think it would be such a complicated formula.

 

[Fluff]

You're welcome & thanks for the feedback.