# help with funtion to search for information, maybe VLOOKUP?

1. ## help with funtion to search for information, maybe VLOOKUP?

Hi everyone. I have a question. By the way, I am using Excel 2010. I have a spreadsheet with some orders (usually it has several hundred rows but for simplicity I am attaching one with 10 rows only). As you can see, it has the name of the customer, their email address, the date they placed the order, if the package was sent or cancelled, and what happened with it. Some customers had to place 2 or 3 orders for the same item, since the initial order(s) didn't go through for some reason.

What I am trying to do is to gather the email addresses of ONLY the customers that never received their package so I can contact them and take care of it one way or another. I could go and check one by one and gather each email but as I mentioned, with hundreds of rows it will be very time consuming.

Is there a way I can enter a formula on column G for each row to only display the email address if that customer has NOT received the product they ordered? The challenge is that there are 2 or 3 rows for the same customer so the normal =IF(F2="Package delivered","",B2) would not work because it will display the email address on G3 for Matt but Matt did get his package when he placed the order a few days later on Row 4. The end result should only give me the email addresses of Chris and Bob on cells G8, G9 and G10 respectively (it doesn't matter if G8 gives the email too. I don't mind duplicate emails as long as they are from customer who never received the product)

The other challenge is that there are 2 ways a customer can get the product, Based on the status, the way to know that the customer got the product is if it says "Package Delivered" or "Picked up at store".

I was thinking maybe a VLOOKUP function that searches for different rows. It would need to eliminate John, Matt and Steve for example from displaying their email addresses on column G since all three of them got their packages. Or maybe some other function. I am not very good a coding on VBA so I am trying to stay away from that if possible.

2. ## Re: help with funtion to search for information, maybe VLOOKUP?

Put this in I2, then drag down:
=IF((COUNTIFS(\$B\$2:\$B\$10,B2,\$F\$2:\$F\$10,"P*")>0),"",B2)

Note, it assumes all of the delivered packages texts begin with a 'P' and the others do not begin with a 'P'. Maybe that's a safe assumption, maybe it isn't. A problem I foresee is that if a person ever had any package delivered, then this will not show their name if another intended package was not delivered. In other words, if 'Joe' orders an item and it is delivered, then a week later Joe orders another package but it is not delivered, this formula would not highlight Joe (since he had the first package delivered a week ago).

3. ## Re: help with funtion to search for information, maybe VLOOKUP?

there is a problem with the examples you've provided

row 8 = package sent & cancelled - package returned
row 10 = package sent & cancelled - package returned

but in your expected results you are not interested in row 8, only row 10

4. ## Re: help with funtion to search for information, maybe VLOOKUP?

Very impressive solution Pauleyb. I tried to transfer the same concept to my spreadsheet but I didn't realize my spreadsheets are more complex than the example I sent initially.

I have attached a spreadsheet that is more similar with what I deal with everyday. When I create the formula to show the email addresses on column F for the people who never received the order somehow it gives me all the addresses. I am not sure why. maybe because of the "Status" column (column C), which some of them have blank spaces, I am not sure. Also, more than 2 results determine that the customer received the order: "Order Complete" and "Pending Delivery", so doing the P* option didn't work in this case (although it was very clever). How would you suggest adjusting the formula so it gives the same result like column H, which I did manually?

5. ## Re: help with funtion to search for information, maybe VLOOKUP?

Similar technique, put in H2 and drag down:
=IF(COUNTIFS(\$A\$2:\$A\$48,A2,\$C\$2:\$C\$48,"Order Complete")+COUNTIFS(\$A\$2:\$A\$48,A2,\$C\$2:\$C\$48,"Pending Delivery")>0,"",D2)
The above is similar to my first attempt until I noticed the (inappropriate) 'P' trick.

There are currently 1 users browsing this thread. (0 members and 1 guests)