Hello,
Is there a way to simplify formula in B4 from attached file ?
Current formula in B4: IF(OR(VLOOKUP(A9,$A$9:$F$11,2,FALSE)="TBD",VLOOKUP(A9,$A$9:$F$11,3,FALSE)="TBD",VLOOKUP(A9,$A$9:$F$11,4,FALSE)="TBD",VLOOKUP(A9,$A$9:$F$11,5,FALSE)="TBD",VLOOKUP(A9,$A$9:$F$11,6,FALSE)="TBD"),"Yes","No")
The formula indicate "Yes" if the word "TBD" is found in the row for ID 77855000
One way:
Formula:
Please Login or Register to view this content.
Trevor Shuttleworth - Retired Excel/VBA Consultant
I dream of a better world where chickens can cross the road without having their motives questioned
'Being unapologetic means never having to say you're sorry' John Cooper Clarke
Another way, =IF(ISNUMBER(MATCH("TBD",XLOOKUP(A4,$A$9:$A$11,$B$9:$F$11),0)),"Yes","No").
Try this.
Formula:Please Login or Register to view this content.
Amazingly equal.
Last edited by DJunqueira; 03-06-2024 at 09:55 PM.
Amazing, the proposed formulas cut the original by more than half. That is very helpful as the sample is searching only thru 5 columns, but the actual file is looking over 50 columns.
Very helpful. Thank you both for the quick response!
Just one cell formula now.
Formula:Please Login or Register to view this content.
Last edited by DJunqueira; 03-06-2024 at 09:57 PM.
Thank you all for your input, I will try all of them to see which formula produce the smaller file size across hundreds of thousands of records.
You're welcome. :
You may not be aware that you can thank those who have helped you by clicking the small star icon located in the lower left corner of the post in which the help was given. By doing so you can add to the reputation(s) of those who helped.
Just playing with DJ's solution. To make it easier to add search or look up rows, or increase the colour columns, try this:
Formula:Please Login or Register to view this content.
Cell B4 formula
Formula:Please Login or Register to view this content.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks