Re: Vlookup across Multiple Sheets with multiple results
Ok let me rework the example workbook so you can take a look
Re: Vlookup across Multiple Sheets with multiple results
Ok let me rework the example workbook so you can take a look
Re: Vlookup across Multiple Sheets with multiple results
[ATTACH=CONFIG]39819[/ATTACH]Take a look at this
Re: Vlookup across Multiple Sheets with multiple results
It works nicely, and quickly. Do you think it will be quick with up to 3,000 lines?
Also, is it still possible to show no results when G2 is empty?
Re: Vlookup across Multiple Sheets with multiple results
I think it will still be very fast.
Yes, in the function sheet, change the formula underneath Search2-4. You will be looking for Len($A$2). Change it to $A$3 for Search2, $A$4 for Search3, and $A$5 for Search4
Re: Vlookup across Multiple Sheets with multiple results
and adding columns, how would I go about doing that?
Thank you for all your help with this!
Re: Vlookup across Multiple Sheets with multiple results
This is the formula on sheet4. Simple replace COL with a column name (e.g. D, F, AA, BA, DB)
=IFERROR(INDIRECT(CONCATENATE("'",Function!$A$8,"'!COL",SMALL(Function!$F$2:$F$500,ROW()-1))),"")
Re: Vlookup across Multiple Sheets with multiple results
Brainfart on my end, but does this go into a new cell on Sheet4, or modified an existing formula?
Re: Vlookup across Multiple Sheets with multiple results
Either way
Re: Vlookup across Multiple Sheets with multiple results
Hey, it's me again.
On the function tab, it is finding matches, but past the 600th line, it doesn't display on Sheet4. I have it set to read up to 1000 lines, but have no idea why this isn't transfering to Sheet4. Any thoughts?
Nevermind! Just had to increase the value on the formula in Sheet4.
Re: Vlookup across Multiple Sheets with multiple results
Look at the formula. You might have to extend one of the ranges
Re: Vlookup across Multiple Sheets with multiple results
I tried to recreate your error with no luck. What do you mean it pulls from 804? It pulls line 804?
Re: Vlookup across Multiple Sheets with multiple results
I narrowed it down and realized that the number 0 does not cooperate. I just changed it to an uppercase O and settled on that.
If I were to colorcode the list that information is pulled from, is there a chance the color could also travel with the information for the results?
Re: Vlookup across Multiple Sheets with multiple results
Without VBA, no. With VBA, yes.
Re: Vlookup across Multiple Sheets with multiple results
Well, shoot... I guess that's what happens when so many limitations are placed on an Excel project!
Re: Vlookup across Multiple Sheets with multiple results
Yea. Sorry about that
Re: Vlookup across Multiple Sheets with multiple results
One final question (hopefully) and I think I have this formula madhouse understood from top to bottom.
I modified the 'find' field from G2 to B2 and changed the formulas to match. However, with the old 'Sheet4', the results started on A2, but on the new 'Sheet4', the results start on A5, but some results are not included. From 2 - 4, it doesn't show.
Is there something i'm missing in a formula? Some type of range?
Re: Vlookup across Multiple Sheets with multiple results
Do the matches show up on the function sheet?
Re: Vlookup across Multiple Sheets with multiple results
Yup, they show up on the function sheet.
Re: Vlookup across Multiple Sheets with multiple results
Then I would have to presume that it's something on sheet4. Give this a shot. On sheet4 put somewhere =COUNTA(RANGE) RANGE being the range of data that shows up. You should get a number that represents how many items there are. Go to the function sheet. Where there is a column of row numbers and falses at the top you should see a number. That is the count of how many matches there are. So in the grand scheme of things, the numbers should be the same. What two numbers do you get?
Don’t have an account yet? Register yourself now and be a part of our community!