Vlookup with search criteria

[davidhall]

I have three search terms: Actual, Mixed, and Current. On sheet 1, cell B1, this needs to search for the terms on sheet 2 which are always found in column B. The search should first look for Actual and return the word "Actual", if that isn't on sheet 2 then it should proceed to look for Mixed and return the word "Mixed", if that isn't on sheet 2 then it should finally look for Current and return the word "Current". It must be in that order. The search area on sheet 2 is from B1 to C150 and the data needed will always be in column B (the second column).

Any suggestions?

 

[bryonwoods30]

are you choosing one of your search terms on sheet1 cell b1 and you want it to return back information from sheet 2 based on that sheet1 cell b1 cell criteria? does column b on sheet 2 have multiple instances of your 3 types? and what information are you wanting to return and where to.

 

[davidhall]

There are three possible terms that need to returned to cell B1. The first term searched on sheet 2 should be Actual. If that word is on sheet 2, B1 should display Actual and ignore searching for the next two terms. If actual isn't on sheet 2, the next search term should be Mixed. If Mixed shows up, the word Mixed should be returned to cell B1. Lastly, if neither Actual or Mixed is found, the last criteria to be searched is Current. Current is always displayed on sheet 2 but the Actual and Mixed should precede Current.

Sheet 2 could display Actual and Current but the search term should only return Actual. Sheet 2 could also display Mixed and Current but should only return Mixed. In other words, Current will always be displayed.

 

[bryonwoods30]

if you just want to return actual, mixed or current to that one cell this formula works, please the K:K with the column in sheet two you are looking at.

=IF(COUNTIF(K:K,"actual")>=1,"actual",IF(COUNTIF(K:K,"mixed")>=1,"mixed","current"))

 

[davidhall]

Thanks a lot. That works great!