Vlookup same reference and return value if different

Hi

I have a sheet that contains a list of stores for which there may be multiple entries of the same store. The multiple entries relate to the order number that has been raised.
Sheet 1 (Orders) lists the locations that have placed an order and the order number.
On sheet 2 (Orders by Store) I am using '{=IFERROR(INDEX(OFFSET(Orders!$A$2,0,0,COUNTA(Orders!$A:$A),1),MATCH(0,COUNTIF($A$1:A1,OFFSET(Orders!$A$2,0,0,COUNTA(Orders!$A:$A),1)),0)),"")} in column A, to list each store and =IFERROR(VLOOKUP($A2,Orders!A2:B11,2,FALSE),"") in column B to get the associated Order Number.

What I want to achieve is to list the stores but if there are multiple entries with a different order code then list it a 2nd time with the new code.

Bit of a challenge but any help very much appreciated. Example file attached.

Martin

Thanks Fotis1991, I'll try it with real data and get back to you.

Thanks Fotis 1991

Although I showed the order data in a separate sheet in the same workbook, the reality is the data I want to look up is in another workbook and I seem to get no match when I change Orders! to C:\Documents and Settings\Martin\Desktop\Reports\[MonthlyOrders.xls]Data'!$Q$2:$Q$200.

Is indexing limited to the same workbook?

Hi Fotis

Unfortunately the real data is always supplied in a separate workbook and I'm not allowed to cut and paste from it. Also the real data may be posted in a shared directory on the server. Is there a way to get the same results by referencing the file like you can with Vlookup?

Perfect! Works a treat.

Many thanks for your help and excellent knowledge.

Hi Fotis

I'm trying to simplify the formula by integrating the data into the master workbook but running into problems, I think with the offset.

I have a sheet which lists stores that have placed orders "orders Placed by Store"
I have a sheet that stores have submitted with the order number "Orders"

Using the list of "Orders Placed by Store" in column B, I need to lookup if the store is listed in the "Orders" tab, I then need put the order code in column C without repeating the previous order code.

The previous solution worked but listed all order codes first then determined which Store.

I need to use the "stores Ordered", column B, as the lookup value.

Sorry to be a pain but orders placed by Store is a format that's fixed and I can't manipulate.

Workbook attached.

Regards
Martin

Fotis

This appears to work but I can't add any columns with new data e.g column A. Not quite sure how "B2&A2" works?

The goal is basically the same, to check the Orders to see if a store has submitted the order number. They may have submitted more than one or none at all.
The stores ordered, listed in column B, are the ones where we expect to see an order, so I'm trying to see if the store has completed the Orders sheet and added the correct GRN number.

In reality the "Orders tab" is a separate workbook but i think we fixed that yesterday.

Hope this makes sense

I think I get it thanks

Unfortunately this won't work as I can't insert or delete columns to accommodate the countif statements. I think you were on the right track with
=IFERROR(INDEX(OFFSET(Orders!$B$2,0,0,COUNTA(Orders!$B:$B),1),MATCH(0,COUNTIF($D$1:D1,OFFSET(Orders!$B$2,0,0,COUNTA(Orders!$B:$B),1)),0)),"") in the first sheet you sent. but need to make sure the store has an order using column B "Stores Ordered" as the lookup reference.

Hi Fotis

I'm really sorry I didn't see this post as it was on page 2 of 2 and I thought you had not responded. (I wasn't aware it paged the responses)

In answer to your question, a store notifies head office that an order is being placed. When the warehouse receive the request they generate a GRN and advise head office
So Branch A120 will raise an order on Monday and head office add it to the forecast. When they receive the GRN from the warehouse it's on a different system. On Wednesday the same branch A120 raise another order and a new GRN is generated. At the end of the month I check to see if all the orders that were raised have been fulfilled and and all the associated GRN's are completed.

So for example branch A120
A120 = GRN 0001
A120 = GRN 0009
A120 = Not Found
A120 = GRN 0050

Hope this explains.