Re: VLOOKUP return multiple values
Currently A3 is populated by one name, with nothing below it. My issue is that i was able to put the formula above into a worksheet in my 2003 excel. populate a table using column F filled with names, Column G filled with items, and column H filled with order dates. However when i try to move the "search area" from column "F" to another column lets say column "L" i dont get any results and i cant figure out why.
Re: VLOOKUP return multiple values
So i was able to make the following work for me
=OFFSET(F1,SMALL(IF(F2:F5000=A3,ROW(F2:F5000)-ROW(INDEX(F2:F5000,1,1))+1),ROW()-2),COLUMN()-1)
note im using excel 2003!
However for some reason i cannot seem to adjust the formula to search a new table, my end goal is to have this formula on "sheet 1" searching for text on "sheet 1" in a table on "sheet 2", but again im stuck at even shifting from one table to another on the same page
Re: VLOOKUP return multiple values
Hey Richadj4, turns out the version of excel on my work computer is the cause for that formula not working, just loaded my book on my personal laptop and tried that code with excel 2016 and works perfect.
Re: Search table return multiple value's
I attempted this formula two ways, altering to fit my spreadsheet with no luck, as well as altering my spreadsheet to mimic my table above again with no luck. is it worth noting that im running excel 2003? (13 year old program i know, but it's suiting my needs for work)
Re: Search table return multiple value's
So with this formula i can alter "5000" down to the actual size of the table ill be searching through, as even when the table gets altered it will not get any larger than what it is? if i then want to return the time value i change the 2 in bold?($F$2:$F$5000,1,1))+1),ROW()-2),COLUMN()-1),"")
or would it be one of the "1's" ?
What do you mean by dynamic ranges instead of absolute addresses? if you have time to explain i have the time to learn something new.
Thank you for all your help Richadj4 i will be entering this into my workbook shortly to test out.
Re: Search table return multiple value's
Quote from richadj4;784720Display More1) are you searching multiple columns for the text in A3? (as written this would only return a result if the A3 text is in column AI)
2) Do you have an upper bound on the number of results?
3) Is column AI sorted, or can it be?
4) Are you looking for a formulae solution (possible but difficult-very difficult depending on above answers) or is VBA acceptable?
5) sample data?
Thank you for your reply, i have not ventured into using VBA's as of yet, this may be the tipping point in starting to learn and use them. to answer your questions,
1) the text im searching against (a3) will only show up in column AI, one set of answers im looking for will be in column AJ, i intend to adapt whatever answer i get to also get text from AK.
2) im not sure i understand? if your refering to the maximum number of results probably no more then two or three.
3) Column AI through AK is a populated directly from another worksheet, so i dont believe (at my novice level) that i can sort.
4) my original hope was a formula based solution, but like i mentioned before that if this is the tipping point to getting into VBA's so be it
now for some sample data
[TABLE="width: 500"]
Column A
[/td]column b
[/td]column c
[/td]column d
[/td]skip
[/td]column AI
[/td]column AJ
[/td]column AK
[/td]1
[/td]Names
[/td]Horse
[/td]Time
[/td]2
[/td]Name
[/td]Horse
[/td]Time
[/td]Jane
[/td]Shilo
[/td]8:30
[/td]3
[/td]Jane
[/td]Shilo
[/td]8:30
[/td]John
[/td]Medoro
[/td]9:15
[/td]4
[/td]George
[/td]10:00
[/td]Jane
[/td]George
[/td]10:00
[/td]5
[/td]Joe
[/td]Fidoro
[/td]10:45
[/td]
[/TABLE]
So my ideal situation A3 is populated from another worksheet, the same goes for AI2:AK5. Thus triggering b3:c5 to get populated by all instances where A3 is found in AI2:AK5
Hope this information helps! Thanks again for the help
Hey everyone, im looking to create a group of cells to search a table and return what it finds, in my case there may be multiple results.
here is the formula im using that searches the table for text in a certain cell then returns text from the cell next to the keyword
=IF(ISNA(VLOOKUP(a3, ai2:ak34, 2, FALSE)),"",VLOOKUP(a3, ai2:ak34, 2, FALSE))
a3= desired search keyword
ai:ak34 = the table i want to search
2=what column in the table i want the search to return with
like i mentioned my issue comes down to when the text in "a3" is mentioned more than once, the only answer i get is the first one it finds, and in my case it would be inconvenient to break down the table and make multiple searches.
Thank you in advance
Re: Subtracting time depending on text in a previous cell
You caught me! and saved me twice today, again thank you!
Re: Subtracting time depending on text in a previous cell
That brings up "#name?"
Re: Subtracting time depending on text in a previous cell
Now long shot here, if my formula is " =IF(H40="David",J80,J80-TIME(0,15,0)) " and if it has no data to return and comes back #Value! how can i hide it? Ive used "=IF(ISNA " to hide another formula but can tweak it to hide this formula/error
Re: Subtracting time depending on text in a previous cell
Sorry for jumping the gun! It worked perfectly!
Re: Subtracting time depending on text in a previous cell
im testing it out now
Re: Subtracting time depending on text in a previous cell
Quote from AliGW;783861Perhaps this:
=IF(BB60="John",BD60,BD60-TIME(0,15,0))
Close, but i need the opposite, if said cell does not contain "John" 15 minutes needs to be subtracted.
its a start and much appreciated!
Re: Trying to retrieve Data over multiple Rows and Columns
Look at each formula, when you copy, move formula's they get altered automatically, you may have to go back into each cell and change where it searches. The best way i have found to do this is to go into "formula Auditing mode" copy all the cells you need to fix the formula from, then open up notepad paste, then manually edit everything, copy again and paste back into the first cell.
[TABLE="width: 500"]
A
[/td]B
[/td]C
[/td]Horse
[/td]Rider
[/td]Time
[/td]1
[/td]Lucky
[/td]John
[/td]12:45
[/td]2
[/td]Venus
[/td]Jane
[/td]1:45
[/td]3
[/td]Zelda
[/td]Joe
[/td]2:15
[/td]
[/TABLE]
Im making a scheduling workbook, my current task im trying to accomplish involves organising ride times for horses. Column 1 Contains Horses, Column B riders, and C time. In my scenario if a student is riding they get on the horse 15 minutes prior to the time, if the trainer is riding he gets on at the set time in column C. Can someone help devise a formula that if cells in column B does not contain the word "John" to subtract 15 minutes from the adjoining cell in column C.
At the point of the document where i will be adding this formula, each cell if populated from another cell in the worksheet. so the cells actually look as such.
[TABLE="width: 500"]
Horse
[/td]Rider
[/td]Time
[/td]1
[/td]lucky
[/td]=bb60
[/td]=bd60
[/td]2
[/td]venus
[/td]=bb61
[/td]=bd61
[/td]3
[/td]zelda
[/td]=bb62
[/td]=bd62
[/td]
[/TABLE]
Any help would be much appreciated, i attempted an =If() formula, and will continue to try make it work unless anyone can help.
Re: Need some help
Thanks alot Max. i will look into Working with a macro at a later point. For now the formula you gave will suffice.
Quote from Max1616;762929You would have to add the developer tab onto your ribbon, open VBA, create a module and paste the code within the module. Then you can run the code from the developer tab on your ribbon when you click the "Macros" button. Or you can assign the Macro to a shortcut key.
Re: Need some help
the second formula works perfect! now just need to figure out how it works (ill do that on my own) what would running a macro entail?
Re: Need some help
=IF(LEN(TRIM(E4))<>0,B4,"") brought over all the names of those who paid, im looking for th eopposite, the names of those who didnt pay, also when it brought the names over it would leave blank cells, for instance it would bring "jane" to I3 and "Larry" to I7, when i would like it to bring b2, b4,b5, b6, b8, etc etc to column i without empty cells
[TABLE="width: 500"]
a
[/td]b
[/td]c
[/td]d
[/td]e
[/td]f
[/td]g
[/td]h
[/td]i
[/td]1
[/td]Name
[/td]CHK #
[/td]Amount Due
[/td]Amount Paid
[/td]Projected gross
[/td]Received gross
[/td]Still owe
[/td]2
[/td]john
[/td]$xxx,xxx
[/td]$x,xxx,xxx.xx
[/td]$xxx,xxx.xx
[/td]john
[/td]3
[/td]jane
[/td]xxx
[/td]$xxx,xxx
[/td]$xxx,xxx
[/td]bob
[/td]4
[/td]bob
[/td]$xxx,xxx
[/td]billy
[/td]5
[/td]billy
[/td]$xxx,xxx
[/td]lisa
[/td]6
[/td]lisa
[/td]$xxx,xxx
[/td]etc
[/td]7
[/td]larry
[/td]xxxx
[/td]$xxx,xxx
[/td]$xxx,xxx
[/td]etc
[/td]8
[/td]etc
[/td]$xxx,xxx
[/td]9
[/td]etc
[/td]$xxx,xxx
[/td]10
[/td]etc
[/td]xxxxx
[/td]$xxx,xxx
[/td]$xxx,xxx
[/td]11
[/td]etc
[/td]xxx
[/td]$xxx,xxx
[/td]$xxx,xxx
[/td]12
[/td]etc
[/td]$xxx,xxx
[/td]13
[/td]etc
[/td]$xxx,xxx
[/td]14
[/td]$xxx,xxx
[/td]15
[/td]$xxx,xxx
[/td]16
[/td]17
[/td]18
[/td]19
[/td]20
[/td]21
[/td]22
[/td]23
[/td]24
[/td]25
[/td]26
[/td]27
[/td]28
[/td]
[/TABLE]
Re: Need some help
Quote from Max1616;762910You can place this in Cell I4 and drag it down:
=IF(LEN(TRIM(E4))<>0,B4,"")
that formula brought back the names of those who paid, instead of the names of those who havent paid. and to add to the difficulty is it possible to consolidate as well? so instead of dragging the names straight across it starts with the list and doesnt leave open cells. its a long shot but would be beneficial.
Re: Need some help
realized right after posting that my thread title is very poor 
but i cant edit it. sorry.
