Find & Match Number, and return the Cell

[Soul]

Hi,

I have two sets of data, The numbers i want to find in the range might look like:
(Col C)
145
145
145
145
146
146
147

And from these i need it to match against the following examples:

(Col F) 600_145

12345_146

23456_152

34567_153

45678_154

54789_155

The numbers Before the underscore in cell F will change to all sorts of number/text, however, the numbers after the underscore will always be numbers, and will always have a match in in column C.

What i need is a formula that will identify (for example) 145 is found after the _ in "600_145", and display the who cell in column X.

The Formula goes on Sheet 4, Which has the list i want to match in Column C.
I need the formula to look/match the value in Column C in the below lists (Found on sheet 3) and return the Cell from Col:F.

COL:E COL:F
(Val-Z) (Val-Y_Val-Z)
145 600_145
146 12345_146
152 23456_152
153 34567_153
154 45678_154
155 54789_155

I have tried a bunch of stuff and keep coming back to:

=IF(MATCH('Sheet 4'!C2,'Sheet 3'!E:E,0),'Sheet 3'!F2,""),

And the problem that gives me is that i haven't told excel that COL:E and COL:F are related like that, so it is basically just reproducing the COL:F list.

I think: Sums up where im at.

Please help!

[Azumi]

You're explanation isn,t clear enough, would you try to upload your sample workbook?

Thanks

[Soul]

Im unsure if this is the correct way to supply and attachment/example of a workbook, but here goes...

So in "Sheet 3" i have put where i want the formula in Cell E2.

On this workbook, there is a list of "observations" in Column C.

In "Sheet 2" there is a list of Observation numbers in column A, and a ReferenceNumber_ObservationNumber in column B.

What i need the Formula to do is match/lookup/find the observation number from (Sheet3-Column C), to either column in Sheet2 so it will display the relevant "Reference_Obs#" in Sheet3-Column E.

[Soul]

I have added a sample of the workbook below.

Thanks for your reply!

[bgoree09]

Good morning,

Use this and drag down:

=VLOOKUP(C2,Sheet2!$A:$B,2,FALSE)

Have a good day,

[Soul]

ExampleVer2.xlsx

It astounded me how you could so easily answer this, like it wasn't even hard - i spent hours playing with the formula for this, and came up Nothing! hahah, Thank you so much.
Since i was able to make sense of your answer i have now learned a bit about how Vlookup works, However i have spent 4 hours trying to replicate a similar formula.
I really didn't want to have to come back and bug you all again, but this specific request is doing my head in.

I have re-attached a version two of my original example, Again, its a matter of lookup/match and return:

In Sheet 3, i want to use my newly listed data in Sheet-3 Column-E, to match/lookup the same values found in Sheet-2 Column-F, and return the appropriate value from Column-A.
- This example is pretty much the same as my original, however, there are now other cells in the way of the data im trying to use as a range. I have tried being fancy and naming the columns as a range, and all the little things i know about using them, But for some reason i cannot get it to work due to the cells in-between the two columns in sheet two (the two columns the formula is concerned with are 'A' and 'F'.

Thank you very much for your help guys!

[martindwilson]

=INDEX(Sheet2!A:A,MATCH(C2,Sheet2!E:E,0))

[Zack Barresse]

To add some enlightenment to martindwilson's solution, the reason you can't use VLOOKUP() here is the array, or range, you're looking at has the return column to the left of the lookup column. VLOOKUP() is great, but it always looks in the left-most column of the array, then returns x columns to the right of that.

Here is a great reference card from Microsoft on VLOOKUP(). It's a free download and prints on a single page, so you can hang it up in your office if you'd like.

http://office.microsoft.com/en-us/ex...101873816.aspx

I would, however, add some error trapping to martindwilson's formula. Since they used a 0 as the third syntax of MATCH(), it will always find an exact match. So if no exact match is found it will return #N/A error. To suppress this, use the IFERROR() formula...

=IFERROR(INDEX(Sheet2!A:A,MATCH(C2,Sheet2!E:E,0)),"Not found")

Replace the second parameter of the IFERROR() function to return whatever you want if no match was found.

HTH

[Soul]

=INDEX(Sheet2!A:A,MATCH(C2,Sheet2!E:E,0))

Thank you very much!

[Soul]

To add some enlightenment to martindwilson's solution, the reason you can't use VLOOKUP() here is the array, or range, you're looking at has the return column to the left of the lookup column. VLOOKUP() is great, but it always looks in the left-most column of the array, then returns x columns to the right of that.

Here is a great reference card from Microsoft on VLOOKUP(). It's a free download and prints on a single page, so you can hang it up in your office if you'd like.

http://office.microsoft.com/en-us/ex...101873816.aspx

I would, however, add some error trapping to martindwilson's formula. Since they used a 0 as the third syntax of MATCH(), it will always find an exact match. So if no exact match is found it will return #N/A error. To suppress this, use the IFERROR() formula...

=IFERROR(INDEX(Sheet2!A:A,MATCH(C2,Sheet2!E:E,0)),"Not found")

Replace the second parameter of the IFERROR() function to return whatever you want if no match was found.

HTH

You guys are Out-Standing!
Thank you all very much, the wealth of knowledge here is just phenomenal!

Just to explain the purpose for all this:

I have a program that spits out a CSV file with all the details of an observation, and a sheet with all the notes and specifics of the observations.
This data then needs to be re-organized in to a very specific template (Still CSV format), so it maybe imported to another program.
What i am building is an excel document i can just put the original data into, and have it organize all the fields for me, then copy all the outputs to a CSV file ready for import.

As you have probably already noticed, i am less than novice when it comes to the formulas excel uses, and am very appreciative for the help i am receiving here.

Thank you all very much!