Column A & B Match then select cell from a variable adjacent column on same row

[Belinea2010]

My other attempts at solving what I need does not seem to be working so I am trying a different approach.


I need a formula or a macro or VBA code that...

Looks at all of Column A for the Number 1 and then looks at the whole of Column B for the Number 1.

When it finds the row where these two numbers match then it places the value from the cell directly to it's right (Column C) from the matching row in to Cell G1 but I will need to be able to change it so that it may say that it should show the contents of the cell on the matching row from two columns to the right (Column D) or maybe three columns to the right (Column E) and so on.

I have to do it this way because the match between column A and Column B can happen any where down the column so I specify which Cell in Column C to show in G1.

I have attached (hopefully) an example spreadsheet.

I don't know how else to explain this question...

Thanks everyone.

[protonLeah]

Start with:

Please Login or Register  to view this content.

[Tony Valko]

This array formula** entered in G1:

=INDEX(C1:C12,MATCH(1,(A1:A12=1)*(B1:B12=1),0))

** array formulas need to be entered using the key
combination of CTRL,SHIFT,ENTER (not just ENTER).
Hold down both the CTRL key and the SHIFT key
then hit ENTER.

To return data from other columns just change the red highlighted range to suit.

[Belinea2010]

Start with:

Please Login or Register  to view this content.

Hi Thank you so much, this works great.

I am going to use Tony Valko's suggestion as it also works but is easier for me to understand and modify.

I really appreciate you taking the time to help.

Thanks again

[Belinea2010]

This array formula** entered in G1:

=INDEX(C1:C12,MATCH(1,(A1:A12=1)*(B1:B12=1),0))

** array formulas need to be entered using the key
combination of CTRL,SHIFT,ENTER (not just ENTER).
Hold down both the CTRL key and the SHIFT key
then hit ENTER.

To return data from other columns just change the red highlighted range to suit.

Hi Tony

Thanks for the post and for taking the time to help.

Your code works excellently and does exactly what I need.

Thanks again

[Belinea2010]

This array formula** entered in G1:

=INDEX(C1:C12,MATCH(1,(A1:A12=1)*(B1:B12=1),0))

** array formulas need to be entered using the key
combination of CTRL,SHIFT,ENTER (not just ENTER).
Hold down both the CTRL key and the SHIFT key
then hit ENTER.

To return data from other columns just change the red highlighted range to suit.

Hi Tony

Thanks for the post and for taking the time to help.

Your code works excellently and does exactly what I need.

The only thing I notice is where Column A & B have a match but the is no text to return in
the adjacent cells your code returns the number 0, is there a way so that it returns nothing?

Thanks again

[Tony Valko]

Like this...

=IF(INDEX(C1:C12,MATCH(1,(A1:A12=1)*(B1:B12=1),0))="","",INDEX(C1:C12,MATCH(1,(A1:A12=1)*(B1:B12=1),0)))

Still array entered.

[Belinea2010]

Like this...

=IF(INDEX(C1:C12,MATCH(1,(A1:A12=1)*(B1:B12=1),0))="","",INDEX(C1:C12,MATCH(1,(A1:A12=1)*(B1:B12=1),0)))

Still array entered.

Hi Tony

Wow thanks for the fast reply...

One last question that Ive just encountered while learning how to use your array,

How do I stipulate that Columns A & B are in a different worksheet....

Say columns A & B are in a worksheet called 'Data_Input' but your array will be on a worksheet called 'Results' ?

Would I use Data_Input!A1:Data_Input!A2 or even Data_Input!A1:A12 ?

[Tony Valko]

Like this...

=IF(INDEX(Data_Input!C1:C12,MATCH(1,(Data_Input!A1:A12=1)*(Data_Input!B1:B12=1),0))="","",INDEX(Data_Input!C1:C12,MATCH(1,(Data_Input!A1:A12=1)*(Data_Input!B1:B12=1),0)))

Still array entered!

[Belinea2010]

Dont worry about it, I just figured it out.

I cant tell you how frustrated I have been over this and now, thanks to your array (and protonLeah's suggestion)
it's solved.

Thanks again

[Belinea2010]

just noticed you replied, what a star, thank you again.

[Tony Valko]

You're welcome. Thanks for the feedback!


If your question has been solved please mark the thread as being solved.

In the menu bar above the very first post select Thread Tools, then select Mark this thread as solved.

[Belinea2010]

Hi Tony

So sorry to have to come back to you yet again....


The array is throwing up a different error now. When there is no data for the array to get (eg. A blank row) Its now showing #N/A.

This is a bit of a pain as I have other formulas that look at your array and when the cell is empty (eg. there is no row for the array to return) they do something specific but of course as your array is returning #N/A the other formulas see that as a cell that has data.

i know you have help me out plenty already but I would be very grateful this one last time.

Thanks Tony...

[Tony Valko]

Maybe this...

=IFERROR(IF(INDEX(Data_Input!C1:C12,MATCH(1,(Data_Input!A1:A12=1)*(Data_Input!B1:B12=1),0))="","",INDEX(Data_Input!C1:C12,MATCH(1,(Data_Input!A1:A12=1)*(Data_Input!B1:B12=1),0))),"")

Instead of an error that will return a blank.

Still array entered.

[Belinea2010]

Outstanding Tony - works like a dream.

You really have saved me so much frustration, there is no way on earth that I would have been able to work out any of this on my own.

It is my firm belief that excel formulas are a dark art only practiced by extremely knowledgeable excel wizards

Thank you again Tony

[Tony Valko]

You're quite welcome!