Forum Discussion
Creating a VLOOKUP Formula
Hi, I wandering if anyone could help me. I am trying to create a formula in one sheet that will search if the corresponding item has been returned for a refund in another sheet and if it has to leave the date it was returned.
Sheet 1 - This is the Sales Sheet. Items which sell are written up. In Column A: the user will type in the Stock number of the item that has sold. In Adjacent Columns: they will type in the date, invoice number and status (Sold/Refund). I have written VLOOKUP formulas for Column's E,F and H - which searches for the item in column A in Sheet 2 (Products) and it transposes the data over.
I need help writing a formula in Sheet 2(Products) in Cell M8. It needs to look for product in Column A(A8) in Sheet 1 (Sales) and if it finds it, its needs to check the status of it in Column D (Sold/Refund) if it says Refund it then will need to return the date in Column B. If it says Sold M8 in Sheet 2 will need to remain blank.
5 Replies
Try this one in M8 on your second sheet:
=IF(VLOOKUP(A8,Sheet1!A:J,4,FALSE)="Returned",VLOOKUP(A8,Sheet1!A:J,2,FALSE),"")
Replace "Sheet1" by the actual name of your first sheet.
Thank you for your reply. It works which has really helped me.
However, if the item being sold e.g: R1 has been written twice into Sheet 1 "Sales" as it has sold first and then it has been returned. The formula will not show the return date in Sheet 2 "Products".
It only shows the return date if it has not been sold first.
For example in the images I have taken: In Sheet 1 "Sales" - R3 has been refunded without being sold and Sheet 2"Products" shows this return date. But the item R1 (in Sheet 1"Sales") that has been sold first and then returned after does not want to show this return date in Sheet 2"Products".
As @Patrick2788 already mentioned in his earlier response (and I omitted to do so), VLOOKUP doesn't work when you have duplicates. You need to find a way to make your items unique. E.g. by combining S/Number and Pink, as it seems that "Pink" is the receipt number for either a sale or a refund. For example R1_123457 is then not the same as R1_156789 and you can trace the selling- or the return date of that particular transaction.
Presuming there are no repeats in the products sheet.
You can use something like this (Adjust the ranges accordingly for your data) :
=IF(VLOOKUP(L2,Sales!$A$2:$C$3,3,0)="Refund",Sales!B2,"")
If there are duplicates in the products sheet, you'll need something stronger. Still do-able.
Hi, thank you for getting back to me. Just a quick question, in your formula - what does the "Sales!B2" relate to?
