Excel - combining MID and VLOOKUP? - Alfa Romeo Forum
You are currently unregistered, register for more features.    
The Technology Section A place to discuss technology & gadgets.

 
Thread Tools
(Post Link) post #1 of 14 Old 29-06-15 Thread Starter
Status: Daddy bear
Global Mod Team
 
bazza's Avatar
 
Join Date: Nov 2002
Location: Congleton - The centre of
Posts: 44,520

Member car:

AMG C63 estate

Excel - combining MID and VLOOKUP?

Is this possible?

I want a formula to search column A for a value 'X'. If it finds 'X', then I want it to look across to column F and return a character string from within that field.

Hmm. Thinking aloud (since I'm looking for that strong ti either be "In" or not, I wonder if I could use a countifs instead?

COUNTIFS : column A = X and MID(column F,5,2)="In"

(Obviously that isn't in correct format, but I'm rushing.) Will that work in principle? Can then nest that in an IF which translates it back to an "In" or whatever other text I decide on?

Seen an offensive/spam post? Report it! Click the or button next to the post.
AO Rules - AO Gallery - AO Classified adverts - AO Club - AO Traders

"Human beings, who are almost unique in having the ability to learn from the experience of others, are also remarkable for their apparent disinclination to do so." - Douglas Adams


"You know, sometimes the world seems like a pretty mean place."
"That's why animals are so soft and huggy."
- Calvin and Hobbes

To err is human, to moderate ursine.
bazza is offline  
Sponsored Links
Advertisement
 
Status: Sticking up for MGs everywhere!
AO Silver Member
 
David WB's Avatar
 
Join Date: Apr 2014
Location: United Kingdom
County: Lancaster
Posts: 3,769

Member car:

147 & GQV

Quote:
Originally Posted by bazza View Post
Is this possible?

I want a formula to search column A for a value 'X'. If it finds 'X', then I want it to look across to column F and return a character string from within that field.

Hmm. Thinking aloud (since I'm looking for that strong ti either be "In" or not, I wonder if I could use a countifs instead?

COUNTIFS : column A = X and MID(column F,5,2)="In"

(Obviously that isn't in correct format, but I'm rushing.) Will that work in principle? Can then nest that in an IF which translates it back to an "In" or whatever other text I decide on?
Could you use nested IF statements to perform the same task?

EDIT: Sorry, I missed the last line. Not sure.
David WB is offline  
Status: Sticking up for MGs everywhere!
AO Silver Member
 
David WB's Avatar
 
Join Date: Apr 2014
Location: United Kingdom
County: Lancaster
Posts: 3,769

Member car:

147 & GQV

I forgot how evil Excel is. Can you use a script in Python or such to conduct the same operation?

EDIT: Could you post a minimum working example of a row of data or two?
David WB is offline  
Status: New job, location new car :-)
AO Silver Member
 
AmicusPro's Avatar
 
Join Date: Nov 2009
Location: United Kingdom
County: Nottinghamshire
Posts: 3,490

Member car:

159 SW TBi

I want to say IF THEN ELSE but I'm rusty on working in Excel and would also need a line or two to work with...
AmicusPro is offline  
(Post Link) post #5 of 14 Old 29-06-15 Thread Starter
Status: Daddy bear
Global Mod Team
 
bazza's Avatar
 
Join Date: Nov 2002
Location: Congleton - The centre of
Posts: 44,520

Member car:

AMG C63 estate

I'll post something up in the morning when I'm back in work.
bazza is offline  
(Post Link) post #6 of 14 Old 30-06-15 Thread Starter
Status: Daddy bear
Global Mod Team
 
bazza's Avatar
 
Join Date: Nov 2002
Location: Congleton - The centre of
Posts: 44,520

Member car:

AMG C63 estate

See if this works.

I've created a very pared down example since the data is sensitive.

If you look at the listing tab, just the presence of a 'code' there is enough to show that the item was received. You can see the countif nested in an if I've used in colunb B of Summary to check this. (I know - overcomplicated for this purpose, but the same method is used elsewhere where it does need to be so complicated, so standardisation is good.)

In column C I need to pull across whether the item has then been sent out. If the text in listing column B contains 'In' for the relevant Code, then it has not been sent out. If it contains 'Out' then it has.

But how do I tell excel to look for the line containing the relevant Code, then column B on that line, then the middle of that entry?

I guess I could do it in two steps? A VLOOKUP to a hidden cell, the a MID from that cell? But is there a way to do it in one?
Attached Files
File Type: xls AO example.xls (18.0 KB, 6 views)
bazza is offline  
Status: Sticking up for MGs everywhere!
AO Silver Member
 
David WB's Avatar
 
Join Date: Apr 2014
Location: United Kingdom
County: Lancaster
Posts: 3,769

Member car:

147 & GQV

Quote:
Originally Posted by bazza View Post
See if this works.

I've created a very pared down example since the data is sensitive.

If you look at the listing tab, just the presence of a 'code' there is enough to show that the item was received. You can see the countif nested in an if I've used in colunb B of Summary to check this. (I know - overcomplicated for this purpose, but the same method is used elsewhere where it does need to be so complicated, so standardisation is good.)

In column C I need to pull across whether the item has then been sent out. If the text in listing column B contains 'In' for the relevant Code, then it has not been sent out. If it contains 'Out' then it has.

But how do I tell excel to look for the line containing the relevant Code, then column B on that line, then the middle of that entry?

I guess I could do it in two steps? A VLOOKUP to a hidden cell, the a MID from that cell? But is there a way to do it in one?
Solving this is not possible within the normal bounds of excel I fear. The problem is, you need to return an array reference where Excel can only return values. This is the problem not being able to create array reference variables on the fly. What you really want is to return an array reference. You will have to use the VLOOKUP function and then create a hidden column in order to solve the problem.

Your problem falls apart where you say "But how do I tell excel to look for the line containing the relevant Code, then column B on that line, then the middle of that entry". Excel handles things like

=IF(This, then_do_that, else_do_that)

but there is no way you can recover where 'This' happens and forward it to the 'then_do_that' or 'else_do_that' parts of the formula. It will only tell you if it happened. Same with the VLOOKUP function, and in fact, all of Excel I think. This sort of makes sense as you cannot do that in a scripting language either. What you are really asking is 'how can I make a variable'; you can't sadly, not in the context of a self contained formula:

=IF(This_happens, cell_ref = "Where_this_is_happening", then_do_this_with_cell_ref, or_do_that_with_cell_ref)

We can string search for 'n' in the code column very easily and also parse strings easily. But you can only find how many instances of that code exist, not where they are; more accurately you cannot return them in a single line, hence your problem. You can only create another column which will return an array reference, and then use that particular reference in all of your loops. You could try using VBA, but that means using Visual Basic macro in Excel which is possibly a cause for hanging. I hate Excel sometimes, lovely to look at, but useless for data analysis. :wall:

Excel can do some really cool stuff with graphs, but its inability to do complex searches and return nice variables is annoying. It's well up there with C-Shell scripting in my list of least liked computing activities.

Last edited by David WB; 01-07-15 at 10:49.
David WB is offline  
(Post Link) post #8 of 14 Old 01-07-15 Thread Starter
Status: Daddy bear
Global Mod Team
 
bazza's Avatar
 
Join Date: Nov 2002
Location: Congleton - The centre of
Posts: 44,520

Member car:

AMG C63 estate

Cheers David, that was the conclusion I was coming to, but good to get confirmation I wasn't missing something.
bazza is offline  
CobolMan
Status: - Update
Guest
 
Posts: n/a
bazza, try putting this into C2 of your first tab and copy down:

=IF(B2="Yes",IF(ISNUMBER(FIND("Out",Listing!B2))," Yes","No"),"")

It uses the isnumber and find to search for your string (you could put this into a cell if you want) in the corresponding listings worktab.
 
(Post Link) post #10 of 14 Old 06-07-15 Thread Starter
Status: Daddy bear
Global Mod Team
 
bazza's Avatar
 
Join Date: Nov 2002
Location: Congleton - The centre of
Posts: 44,520

Member car:

AMG C63 estate

I like the use of FIND - haven't used that formula before.

I think there's a problem with your suggestion though. It is assuming that the lines on the listing tab are arranged the same as the lines on the summary tab: i.e. when looking for the answer for Summary C2, it's querying only the field Listing B2. In fact, I need it to look at the whole spreadsheet first to identify which line is the correct match (based on column A). That's the bit which I think is not possible.
bazza is offline  
Status: Sticking up for MGs everywhere!
AO Silver Member
 
David WB's Avatar
 
Join Date: Apr 2014
Location: United Kingdom
County: Lancaster
Posts: 3,769

Member car:

147 & GQV

Quote:
Originally Posted by bazza View Post
I like the use of FIND - haven't used that formula before.

I think there's a problem with your suggestion though. It is assuming that the lines on the listing tab are arranged the same as the lines on the summary tab: i.e. when looking for the answer for Summary C2, it's querying only the field Listing B2. In fact, I need it to look at the whole spreadsheet first to identify which line is the correct match (based on column A). That's the bit which I think is not possible.
Yes, I also tried the above approach (or similar at the time IIRC) with the SEARCH function combined with ISNUMBER, giving a binary result. This also runs into the same problem as you don't have a 'middle' variable that can be set.
David WB is offline  
CobolMan
Status: - Update
Guest
 
Posts: n/a
I think you're right there guys.
 
Status: turbo powwa
AO Silver Member
 
celadon's Avatar
 
Join Date: Nov 2005
Location: United Kingdom
County: Kent
Posts: 5,699
Would you be able to use the MATCH function to find the position of the code (from summary column A) in listing column A, then look in the relative listing column B to find out the result ?

For instance : =MATCH(Summary!A10,Listing!A2:A10,0) will return the value 7, as 9 (the value in Summary!A10)is matched in the 7th row of the array A2:A10
=MATCH(Summary!A11,Listing!A2:A10,0) will return the value 8, as 10(the value in Summary!A11) is matched in the 8th row of the array

and so on...


I would also use a hidden protected tab to do all the formulas on.




[SIGPIC][/SIGPIC]
celadon is offline  
Status: Sticking up for MGs everywhere!
AO Silver Member
 
David WB's Avatar
 
Join Date: Apr 2014
Location: United Kingdom
County: Lancaster
Posts: 3,769

Member car:

147 & GQV

Quote:
Originally Posted by celadon View Post
Would you be able to use the MATCH function to find the position of the code (from summary column A) in listing column A, then look in the relative listing column B to find out the result ?

For instance : =MATCH(Summary!A10,Listing!A2:A10,0) will return the value 7, as 9 (the value in Summary!A10)is matched in the 7th row of the array A2:A10
=MATCH(Summary!A11,Listing!A2:A10,0) will return the value 8, as 10(the value in Summary!A11) is matched in the 8th row of the array

and so on...


I would also use a hidden protected tab to do all the formulas on.
I suspect MATCH will also run into the same issue. We are talking about trying to do this in one sweep, and I just see no way of curing the forwarding variable problem without using a VBA.

After MATCH, you will then require an IF statement to forward the result of MATCH to continue the computation. But MATCH will not hold the location of the result implicitly to be passed forward. You can't extract '7' or '8' from the MATCH and then push it through the IF statement as a result. The result is passed to the top level, so the cell becomes the result of MATCH. What you really want is the variable as mentioned above, or a recursive script, niether of which Excel can handle outside of a VBA.

I think another key point is that these data are incomplete. There is no direct one to one correspondance in the different fields. Item 4 in column A of sheet one may not correspond to the same item, more specifically, the same cell in sheet 2. This is where the issue really kicks in, because then you HAVE to know WHERE the item that corresponds to your sent item is and then forward it all in the same line, which is where the problem arises.

By the time you have learned the VBA, you may as well export these data and learn a high level, verbose scripting language like Fortran, which would solve the issue in three lines of code I would think. Stupid Excel... :wall:

Last edited by David WB; 06-07-15 at 22:06.
David WB is offline  
Reply

Go Back   Alfa Romeo Forum > Misc Lounges > Community Discussions > The Technology Section

Tags
combining , excel , mid , vlookup

Thread Tools
Show Printable Version Show Printable Version
Email this Page Email this Page



Posting Rules  
You may post new threads
You may post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Trackbacks are Off
Pingbacks are Off
Refbacks are Off

 
For the best viewing experience please update your browser to Google Chrome