how do i get a IF formula to look row by row until it finds a match?

L

Legacy 57334

Guest
Really need some help. Ive reached the end of my knowledge. I've managed to create a formula that will compare the data in a table of data with second table of data, and copy data from table2 to table1 if it finds that enough data matches. It works fine if the match is on the same row.

In the example below, table1 in green and table2 is orange. You can see the formula in the screenshot too.
MrExcelExample2.jpg


What i need is for the formula to look for a match not just on the same row, but in all rows of the orange table, one by one, until it finds a match, or reaches the end of the table without a match.
How do i do that?

thank you
Joel
 

Excel Facts

Will the fill handle fill 1, 2, 3?
Yes! Type 1 in a cell. Hold down Ctrl while you drag the fill handle.
In G2 and copied down:
=IFERROR(INDEX($P$2:$P$6,MATCH(1,INDEX(($J$2:$J$6=A2)*($K$2:$K$6=B2)*($Q$2:$Q$6=E2)*($P$2:$P$6>=D2),),0)),"")
 
Upvote 0
In G2 and copied down:
=IFERROR(INDEX($P$2:$P$6,MATCH(1,INDEX(($J$2:$J$6=A2)*($K$2:$K$6=B2)*($Q$2:$Q$6=E2)*($P$2:$P$6>=D2),),0)),"")

thanks tigeravatar, i appreciate the help.
Your solution does most of what i expected, but in the example i give above, all three bob langtons get given the death date 1934.
It shouldnt use the same piece of data twice. So, having used one of the death entries, it shouldnt use it again (sorry, i should have said that before) if i was using IF statements as per my original atempt, on a match i would have added something to one of the death table columns and then checked that column for a value and disregarded if it was present. Im sure there are better ways but that would have worked. I dont know whether such a solution would work in your implementation or not?

Thanks
Joel
 
Last edited by a moderator:
Upvote 0
thinking about it some more, im definately going to need it to flag those second table (orange) rows that it has used, or else to delete them once the data has been merged with the first table (green). Hope that makes sense.
 
Upvote 0
I've managed to adapt the formula for death place field:
=IFERROR(INDEX($Q$2:$Q$6,MATCH(1,INDEX(($J$2:$J$6=A2)*($K$2:$K$6=B2)*($P$2:$P$6=D2)*($P$2:$P$6>=D2),),0)),"")

I dont think excel is going to let me flag the used death data so that i can ignore it going forward.
So, instead, can i update the formula to check the first table (green) for use of the data in a row before making the update, and if it is present, not create another row like that? Birth date might be different, so it cant compare that column, but it could check that a row with first name, last name, birth location, death date and death location didnt already exist matching what it is about to enter on a new row.

I hope that makes sense. I've taken this as far as i can, and i feel very close to the end. Hope someone can help me finish it.
thanks
 
Upvote 0
I've managed to adapt the formula for the death location field:
=IFERROR(INDEX($Q$2:$Q$6,MATCH(1,INDEX(($J$2:$J$6=A2)*($K$2:$K$6=B2)*($P$2:$P$6=D2)*($P$2:$P$6>=D2),),0)),"")

I dont think excel is going to let me flag the used death data so that i can ignore it going forward.
So, instead, can i update the formula to check the first table (green) for use of the data in a row before making the update, and if it is present, not create another row like that? Birth date might be different, so it cant compare that column, but it could check that a row with first name, last name, birth location, death date and death location didnt already exist matching what it is about to enter on a new row.

I hope that makes sense. I've taken this as far as i can, and i feel very close to the end. Hope someone can help me finish it.
thanks
 
Upvote 0

Forum statistics

Threads
1,213,544
Messages
6,114,249
Members
448,556
Latest member
peterhess2002

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top