Excel Nested If-Then Search by a string * * for a specific text?

Question

I need to search a column of data for a specific text and if it finds my string on that row take three pieces of data on the same row but different column and populate them onto a seperate sheet on the same spreadsheet.

Here is my example:
A5 B5 B6 B7 B8 B9
Beef Blue Ribbon Beef BRB $5.00 2.00% Best Quality
Good Beef Jerky JER $6.50 25.00% Med Quality
Beef Ground Beef GB $2.51 50.00% Low Quality
Mixed Hotdogs HOT $1.25 1.00% Mixed/Meat

My example didn't format well, but basically in Column A is a type, Column B is the name, Column C is a abreviation, Column D is a price, Column D is a percent and Column F is a quality.

I need to search by column F for the quality and if it is Best place the name for the meat in Sheet2 under Column B under Title "Best" and then in column B place the abreviation, column C the price, column D the percent. Then repeat and look for another quality doing the same.
Help?

Answer 1

No need to all these crabs
now, in Sheet2, type the text "Best Quality" with the same letter case in A1, in B1 paste this
=INDEX( Sheet1!$A$5:$F$500, MATCH( $A1, Sheet1!$F$5:$F$500, 0), COLUMN()-1 )
Now, do fill this cell (B2) to the right and to down and type in cell A2, A3, A4, etc the values you want to search in F column of Sheet2

Trust me, I am the VBAXLMan, and no one here can solve it better

Answer 2

=LOOKUP("Best Quality", Sheet1!F:F,Sheet1!A:A)

"Best Quality" must match exactly or it won't return the correct value. You could put "Best Quality" or some other query in a cell in Sheet2 (Let's say G4) and change the formula to this:

=LOOKUP(G4,Sheet1!F:F, Sheet1!A:A)

This way, you could change your search just by changing the text in G4.

Answer 3

you in basic terms ought to sort out this functionality as quickly as. This functionality, entered in B1, will do what you decide on for a given column of text fabric strings in column A. =IF(discover("Grocery",A1,"Grocery", IF(discover("monetary business enterprise",A1,"monetary business enterprise", IF(discover("attractiveness",A1,"attractiveness","")))))) as quickly as you have entered it, you could purely drag it down the column (using the formulation take care of which looks interior the backside applicable hand nook of the cellular).

Answer 4

sub findSomething(sText as string)
s1r = 1
do until sheet1.cells(s1r,1)=""
s1r=s1r+1
loop
s2r = 1
do until sheet2.cells(s2r, 1)=""
s2r=s2r+1
loop
for y = 2 to s1r
if instr(sheet1.cells(y,6),sText) then
sheet2.cells(s2r,1)=sheet1.cells(y,2)
sheet2.cells(s2r,2)=sheet1.cells(y,3)
sheet2.cells(s2r,3)=sheet1.cells(y,4)
sheet2.cells(s2r,4)=sheet1.cells(y,5)
end if
next
end sub

Answer 5

You can either use autofilter or pivottables to get exactly what you want. No need for any 'rocket science' if formulas.