What is wrong with my MATCH formula?

[ishpahuja]

Hi guys
My simple MATCH formula is not giving the right answer. Can you help find it?

In row 1, I have the following:

Q3 F2019

Q3 F2019

Q3 F2019

Q4 F2019

Q4 F2019

Q4 F2019

Q1 F2020

Q1 F2020

Q1 F2020

Q2 F2020

Q2 F2020

Q2 F2020

Q3 F2020

Q3 F2020

Q3 F2020

Q4 F2020

Q4 F2020

Q4 F2020

Q1 F2021

Q1 F2021

Q1 F2021

Q2 F2021

Q2 F2021

Q2 F2021

Q3 F2021

Q3 F2021

Q3 F2021

Q4 F2021

Q4 F2021

Q4 F2021

Q1 F2022

Q1 F2022

Q1 F2022

Q2 F2022

Q2 F2022

Q2 F2022

Q3 F2022

Q3 F2022

Q3 F2022

Q4 F2022

Q4 F2022

Q4 F2022

Q1 F2023

<tbody>
</tbody>

In row 3, I have:
Q3 F2019 Q4 F2019 F2019 Q1 F2020 Q2 F2020 Q3 F2020 Q4 F2020 F2020 F2021 F2022 F2023

So I am putting this formula for Q4 F2019:
=MATCH(B3,$A$1:$BB$1,1) and the answer I get is 39 - How is this possible? Shouldn't it result in 6?

Also, if you can help me with advanced formula which will cover F2021 also, that will be great. F2021 will be what we have in Q4 of F2021, but we don't have Q4 F2021 in Row 3, but only in Row 1.

Thanks

 

[joeu2004]

I don't understand exactly what you have in row 1 and 3 and in B3, but I wonder if changing 1 to 0 in the MATCH expression solves your problem, to wit:
=MATCH(B3,$A$1:$BB$1,0).

 

[Twollaston]

Hi guys
My simple MATCH formula is not giving the right answer. Can you help find it?

In row 1, I have the following:

Q3 F2019

Q3 F2019

Q3 F2019

Q4 F2019

Q4 F2019

Q4 F2019

Q1 F2020

Q1 F2020

Q1 F2020

Q2 F2020

Q2 F2020

Q2 F2020

Q3 F2020

Q3 F2020

Q3 F2020

Q4 F2020

Q4 F2020

Q4 F2020

Q1 F2021

Q1 F2021

Q1 F2021

Q2 F2021

Q2 F2021

Q2 F2021

Q3 F2021

Q3 F2021

Q3 F2021

Q4 F2021

Q4 F2021

Q4 F2021

Q1 F2022

Q1 F2022

Q1 F2022

Q2 F2022

Q2 F2022

Q2 F2022

Q3 F2022

Q3 F2022

Q3 F2022

Q4 F2022

Q4 F2022

Q4 F2022

Q1 F2023

<tbody>
</tbody>

In row 3, I have:
Q3 F2019 Q4 F2019 F2019 Q1 F2020 Q2 F2020 Q3 F2020 Q4 F2020 F2020 F2021 F2022 F2023

So I am putting this formula for Q4 F2019:
=MATCH(B3,$A$1:$BB$1,1) and the answer I get is 39 - How is this possible? Shouldn't it result in 6?

Also, if you can help me with advanced formula which will cover F2021 also, that will be great. F2021 will be what we have in Q4 of F2021, but we don't have Q4 F2021 in Row 3, but only in Row 1.

Thanks

Well, what is in cell b3? and is A1 = Q3 F2019

you don't want 1 as your last argument. Change the last 1 to 0 and it should give you 4 (not 6 because it's only going to find the first 1)

 

[ishpahuja]

Well, what is in cell b3? and is A1 = Q3 F2019

you don't want 1 as your last argument. Change the last 1 to 0 and it should give you 4 (not 6 because it's only going to find the first 1)

Yes, Cell A1 = Q3 F2019
and B3 = Q4 F2019

And I wouldn't have 0 as my last argument in MATCH formula because I am not looking for first occurrence, but I am looking for last occurrence of Q4 F2019 in Row 1. So the result from MATCH formula, I am looking for is 6.

 

[ishpahuja]

I don't understand exactly what you have in row 1 and 3 and in B3, but I wonder if changing 1 to 0 in the MATCH expression solves your problem, to wit:
=MATCH(B3,$A$1:$BB$1,0).

Hi thanks for your response. But changing 1 to 0 in MATCH function won't solve my problem as 0 will give me the position of first occurrence while I am looking for last occurrence in the row. So the result from MATCH formula I am looking for is 6.

Cell A1 = Q3 F2019
and B3 = Q4 F2019

Any idea how I can do that?

 

[Marcelo Branco]

Since there are 3 contiguous occurrences of each value in row 1 to obtain the relative position of the last one try ...

=MATCH(B3,$A$1:$BB$1,0)+2

M.

 

[steve the fish]

Or this if they could appear anywhere:

=LOOKUP(2,1/($A$1:$AQ$1=B3),COLUMN($A$1:$AQ$1)-COLUMN($A$1)+1)

 

[ishpahuja]

Since there are 3 contiguous occurrences of each value in row 1 to obtain the relative position of the last one try ...

=MATCH(B3,$A$1:$BB$1,0)+2

M.

Thanks, this works and really simple thank you.

Can you help me with a simple formula that will cover F2021, F2022 and F2023 from Row 3? As of now, I have these results:
3 6 #N/A 9 12 15 18 #N/A #N/A #N/A #N/A

I could use IF formula but I am trying to think if there is a better formula to cover F2021, F2022 and F2023?