Newbie question about VLOOKUP & duplicate return values - tried searching, got confused

[auralfixstation]

Basically, I'm dealing with two different databases of BWAV audio files. One of them (I) reflects my current database, where I have embedded metadata descriptions(I-C), and filenames that are strings of numbers(I-A). The other database (II) has matching embedded descriptions (II-A), but with better filenames (II-C). Both databases also have durations, but they don't match perfectly, and are somehow formatted differently (a whole other topic, it seems).

In trying to pull the filenames from II-C into database I, VLOOKUP gets me some of the way there - the problem is that I'm basically doing (in database I) =VLOOKUP(C1,!'II.csv'$A$1:$C$14,3,FALSE), but there are some duplicate fields in column C, so they only ever return the first instance they come across.

I feel like I need to do something like an INDEXMATCH that will look at both durations and the descriptions - but then there's the issue of the durations not matching perfectly, and being differently formatted - so if there was a way for me to say "match duration within x tolerance". Or, I could try something where it says that if the returned value is already in the column, then look for the next instance of the lookup value.

I realize that I could also just generate my own filenames in the first database, but I'm interested in trying to use some sort of INDEX or other lookup function to do it because I have had other similar scenarios at times.

Oh, and apologies for not using a better formatted tool to show my examples...couldn't get MrExcel HTML to work on Mac Office 2011.

Thank you in advance for any tips!

I - Current database
[TABLE="width: 500"]
<tbody>[TR]
[TD]A[/TD]
[TD]B[/TD]
[TD]C[/TD]
[TD]D[/TD]
[/TR]
[TR]
[TD]S_02183_001.WAV[/TD]
[TD]00:09.5[/TD]
[TD]Vocals - Mouth Pops - Int - CU - Various Pitched Hollow Pops[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]S_02183_002.wav[/TD]
[TD]00:06.2[/TD]
[TD]Vocals - Mouth Pops - Int - CU - Various Pitched Hollow Pops[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]S_02183_003.wav[/TD]
[TD]00:13.0[/TD]
[TD]Vocals - Mouth Pops - Int - CU - Various Pitched Hollow Pops[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]S_029221.wav[/TD]
[TD]00:07.0[/TD]
[TD]Pops - Rubber Pops - CU - Quick Pops of Various Pitches, Some Movement[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]S_029223.wav[/TD]
[TD]00:04.0[/TD]
[TD]Pops - Bottle Pops - CU - Short Hollow Pops of Various Pitches[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]S_029596.wav[/TD]
[TD]00:21.8[/TD]
[TD]Boats Ships - 41' Sailboat Motor - Ext - ECU Start W Splash & Light Rev,Idle, & Engine Off[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]S_029597.wav[/TD]
[TD]02:05.7[/TD]
[TD]Boats Ships - 41' Sailboat Motor - Ext - CU Start & Idle W Splashes[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]S_029598.wav[/TD]
[TD]03:02.9[/TD]
[TD]Boats Ships - 41' Sailboat - Ext - CU Idle, Short Stop, Into Reverse & Back-up W/ Splashes[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]S_029599_001.wav[/TD]
[TD]02:30.3[/TD]
[TD]Boats Ships - 41' Sailboat - Ext - CU Water Trickling Against The Bow[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]S_029599_002.wav[/TD]
[TD]02:09.2[/TD]
[TD]Boats Ships - 41' Sailboat - Ext - CU Water Trickling Against The Bow[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]S_029600.wav[/TD]
[TD]02:23.1[/TD]
[TD]Backgrounds - Harbor Ambiance - Foghorn Blasts - Periodic Blasts W Light Water Laps[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]S_029601.wav[/TD]
[TD]01:53.9[/TD]
[TD]Boats Ships - 41' Sailboat - Ext - MCU Bow Wash W Bg Foghorn[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]
II.
[TABLE="width: 500"]
<tbody>[TR]
[TD]A[/TD]
[TD]B[/TD]
[TD]C[/TD]
[/TR]
[TR]
[TD]Vocals - Mouth Pops - Int - CU - Various Pitched Hollow Pops[/TD]
[TD]0:06[/TD]
[TD]Vocals-MouthPops-Int-CU-V_1.wav[/TD]
[/TR]
[TR]
[TD]Vocals - Mouth Pops - Int - CU - Various Pitched Hollow Pops[/TD]
[TD]0:13[/TD]
[TD]Vocals-MouthPops-Int-CU-V_2.wav[/TD]
[/TR]
[TR]
[TD]Vocals - Mouth Pops - Int - CU - Various Pitched Hollow Pops[/TD]
[TD]0:09[/TD]
[TD]Vocals-MouthPops-Int-CU-V_3.wav[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Pops - Rubber Pops - CU - Quick Pops of Various Pitches, Some Movement[/TD]
[TD]0:06[/TD]
[TD]Pops-RubberPops-CU-QuickPop.wav[/TD]
[/TR]
[TR]
[TD]Boats Ships - 41' Sailboat Motor - Ext - ECU Start W Splash & Light Rev,Idle, & Engine Off[/TD]
[TD]0:21[/TD]
[TD]BoatsShips-41'SailboatMotor.wav[/TD]
[/TR]
[TR]
[TD]Boats Ships - 41' Sailboat Motor - Ext - CU Start & Idle W Splashes[/TD]
[TD]2:05[/TD]
[TD]BoatsShips-41'SailboatMot_1.wav[/TD]
[/TR]
[TR]
[TD]Boats Ships - 41' Sailboat - Ext - CU Idle, Short Stop, Into Reverse & Back-up W/ Splashes[/TD]
[TD]3:02[/TD]
[TD]BoatsShips-41'Sailboat-Ext.wav[/TD]
[/TR]
[TR]
[TD]Boats Ships - 41' Sailboat - Ext - CU Water Trickling Against The Bow[/TD]
[TD]2:30[/TD]
[TD]BoatsShips-41'Sailboat-Ex_1.wav[/TD]
[/TR]
[TR]
[TD]Boats Ships - 41' Sailboat - Ext - CU Water Trickling Against The Bow[/TD]
[TD]2:09[/TD]
[TD]BoatsShips-41'Sailboat-Ex_2.wav[/TD]
[/TR]
[TR]
[TD]Backgrounds - Harbor Ambiance - Foghorn Blasts - Periodic Blasts W Light Water Laps[/TD]
[TD]2:23[/TD]
[TD]Backgrounds-HarborAmbiance.wav[/TD]
[/TR]
[TR]
[TD]Boats Ships - 41' Sailboat - Ext - MCU Bow Wash W Bg Foghorn[/TD]
[TD]1:53[/TD]
[TD]BoatsShips-41'Sailboat-Ex_3.wav[/TD]
[/TR]
</tbody>[/TABLE]

 

[RickXL]

Hi and welcome to the forum,

You could try something like this:

{=INDEX($A$15:$C$21,MATCH(1,($A$15:$A$21=A2)*(($B$15:$B$21-0.5)<B2)*(($B$15:$B$21+0.5)>B2),0),3)}

The first thing to note is that the above is an "array formula". So you don't enter the curly brackets or braces at each end "{}". You enter the basic formula then hit Ctrl+Shift+Enter to complete the formula entry - not just Enter as you would normally. Excel has to add the braces itself.

Once the formula is entered properly then it needs to be copied down into all the required rows. (Don't select all the rows first expecting EXCEL to do it all at once. That means something else to Excel and it is not so easy to get out of!)

I put my lookup table in cells A15 to C21 and the data was above it. The A15:C21 data has to be sorted by columns A and B so everything is in increasing order.

What does it do?
It performs an exact comparison on the data in column A. Then it moves to column B and finds the entries which are within 0.5 of the required value. (I used whole numbers. If you are using Excel times they will be in fractions of a day so 0.5 will be 12 hours! You will need to use smaller values. I would suggest that you use Excel time values rather than text strings to represent times. If that is not already the case you will make some data adjustments.) The two numbers, the 0.5s, can be different so you could use +0.5 and -0,1, say.

How does it do it?
=MATCH(1, something, 0) returns the row number where "something" exactly matches the value 1.
In this case "something" has the form: X * Y * Z. There are three terms separated by multiplication signs. The values of X, Y and Z will be either TRUE or FALSE - effectively the same as 1 and 0, respectively.

In fact X, Y and Z will all be arrays so X might look like: TRUE;FALSE;FALSE;FALSE;FALSE
If Y and Z also had a TRUE in position 1 then MATCH would return the row of the first value. If all the second positions were TRUE, instead, then the second row would be returned. Basically, all three terms have to be true for MATCH to return a row.

When INDEX gets that row, it looks up the value in column 3 of that row - that is what the 3 is for near the end of the formula.

I hope that gives you a start.



[TABLE="width: 64"]
<colgroup><col width="64"></colgroup><tbody>[TR]
[TD="width: 64"]

[/TD]
[/TR]
</tbody>[/TABLE]