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
 

Some videos you may like

Excel Facts

Using Function Arguments with nested formulas
If writing INDEX in Func. Arguments, type MATCH(. Use the mouse to click inside MATCH in the formula bar. Dialog switches to MATCH.

tigeravatar

Well-known Member
Joined
Aug 12, 2011
Messages
760
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)),"")
 
L

Legacy 57334

Guest
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:
L

Legacy 57334

Guest
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.
 
L

Legacy 57334

Guest
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
 
L

Legacy 57334

Guest
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
 

Watch MrExcel Video

Forum statistics

Threads
1,123,478
Messages
5,601,900
Members
414,482
Latest member
morkar

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
Top