Hi there.
I have a spread sheet with various products in one column followed by a 'true' or 'false' column.
I just want to be able to look in the true/false column, determine if its true and if so display the product.
I was trying to do an if with vlookup in the mix but getting really confused, if anyone could provide pointers I would appreciate it.
Are the true and false Boolean values or are they text values?
By default, Boolean values appear in uppercase and will be centered in the cells. Text values are aligned left by default.
Biff
Microsoft MVP Excel
Keep It Simple Stupid
Let's Go Pens. We Want The Cup.
So in column A you have the products. In column B you have true or false.
So do you need a list of al products that have true in column B?
Regards
Fotis.
-This is my Greek whisper to Europe.
--Remember, saying thanks only takes a second or two. Click the little star * below, to give some Rep if you think an answer deserves it.
Advanced Excel Techniques: http://excelxor.com/
--KISS(Keep it simple Stupid)
--Bring them back.
---See about Acropolis of Athens.
--Visit Greece.
A Vlookup will only return the first value it finds that has either a true or false. So if you want to find all products that are true OR false perhaps you might want to use a filter?
Make contributors happy, click on the "* Add Reputation" as a way to say thank you.
Sam Capricci
Hi yes they are bolean I guess in that its all upper case and centered, this has been pulled from an access database that is a tick box
I can't adjust the columns or anything, so basically the column headers are 'product', 'active', 'price', 'quantity'
The pain being two fold, working with that TRUE FALSE in the active column, and if true, it returning back the the other columns
Thanks for any help, I have been scratching my head for days, but I understand this forum took a bit of a beating so it was unreachable.
IN response to Sambo, the plan was to have copy the formula down the page so it looks at each row of the sheet.
Is this what you had in mind...
Data Range
This array formula** entered in D2:
=IFERROR(INDEX(A:A,SMALL(IF(B$2:B$10,ROW(B$2:B$10)),ROWS(D$2:D2))),"")
** array formulas need to be entered using the key
combination of CTRL,SHIFT,ENTER (not just ENTER).
Hold down both the CTRL key and the SHIFT key
then hit ENTER.
Copy down until you get blanks.
Thanks Tony, will give it a go
This is a way to do a backward vlookup =IFERROR(VLOOKUP($D$236,CHOOSE({1,2},B236,A236),2,0),"") where "TRUE" is in cell D236, the product (like auto) is in column A, the true/false value is in col B and it looks at it one row at a time. It worked for me and I didn't have to restructure columns.
I always have troubles with index (wish I could use it but...) so, anyway, if you're interested.
That got it tony, and lets face it I wouldn't have arrived at that ever! I can pick up the V lookup based on that result now, nice.
You're welcome. Thanks for the feedback!
Based on your last post in this thread, its apparent that you are satisfied with the solution(s) you've received and have solved your question, but you haven't marked your thread as "SOLVED",as per our Forum Rule #9. I will do it for you this time.
In future, to mark your thread as Solved, you can do the following -
Select Thread Tools-> Mark thread as Solved.
Incase your issue is not solved, you can undo it as follows -
Select Thread Tools-> Mark thread as Unsolved.
Also, since you are relatively new to the forum, i would like to inform you that you can thank those who have helped you by clicking the small star icon located in the lower left corner of the post which helped you. This adds to the reputation of the person who has taken the time to help you.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks