Find & Match Number, and return the Cell

Soul

New member
Joined
Mar 11, 2014
Messages
7
Reaction score
0
Points
0
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: :frusty: Sums up where im at.

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

Thanks
 
Attached Example

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.
 

Attachments

  • Example.xlsx
    10.1 KB · Views: 17
I have added a sample of the workbook below.

Thanks for your reply!
 
Good morning,

Use this and drag down:

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

Have a good day,
 
bgoree09 is amazing!

View attachment 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!
 
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/excel-help/quick-reference-card-vlookup-refresher-HA101873816.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
 
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/excel-help/quick-reference-card-vlookup-refresher-HA101873816.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!
 
The impossible

View attachment ExampleVer3.xlsx

No im pretty certain that what im trying to do is impossible, But i do not want to take advantage of having you guys just handing me the answers,

So again, i have attached another example of my document.

I am trying to make a formula for Sheet-3 L:L, that will lookup/match the cells in E, to their master record in sheet-2 F:F, and identify which point of a route an inspection was started at.
The routes only have One start and One End value - X and Y. (Y being clockwise, X being anti-clockwise)

When observations on a Route a recorded and the Route was started at X (traveling anti-clockwise), the records need to reflect the distance from Y also. :faint2:

The total distance of a route is recorded in Sheet-2 H:H. - so the relative distance would just be Distance from X minus Total distance: SUM(Sheet2!H2,-Sheet3!K2)

What i am asking here is:
Can someone please tell me the formula's and the order i am to use them, i have tried variations of "IF", though im missing the part where i tell excel that once i find the match in sheet-2, to look at its start point, and then the sum from the total distance.
 
Assuming sheet 2 will have unique set of data (no repetition of reference_obs#), you can use this.. =VLOOKUP(E2,Sheet2!F:H,3,FALSE)-K2
I assume you want the distance to reach the destination. Example, route 600_145 has a total distance of 14 and 5 has already been traveled (observation), then 9 is the result you are looking for.
 
I wonder if it would be easier to just move the columns (i.e. cut/paste) rather than apply formulas? This would work best if you knew what the column headers would always be, and better still if you knew their location wouldn't change. It could all be automated as well, so it performs this at the click of a button, even the saving of the file into a new CSV file. Is that something you'd be interested in?

Edit: I didn't see your latest response. Speaking to that, could you give us what you would expect as a result in column L? I'm not really sure what formula you're looking for. Can you give one or two specific examples of 1) how you need it calculated and 2) the expected result?
 
Last edited:
Back
Top