hlookup or index/match

Hi all,

i have 2 dilemmas

**dilemma 1**

i have a date of purchase in column A
i have a customer number in column B

now if i want to search for a date and for it to display the results from columns C,D,E and F (purchased items) i know i use HLOOKUP

same if i want to search for customer number,

but how would i look for a customer and a date at the same time.

**dilemma 2**

i want to be able to put in a customer number and excel to tell me all the dates they purchased items.

currently i use

={IFERROR(INDEX(Table1[DATE],SMALL(IF(Table1[NUM]=$L$3,ROW(Table1[NUM])-MIN(ROW(Table1[NUM]))+1),ROWS($1:1))),"error")}

and i change the ROWS($1:1) to ROWS($1:2) or ROWS($1:3) etc

to bring in that result, but i want it to bring in unique dates, as a customer might ring us back with another purchase on the same date and i dont want it to duplicate the date.


hope that makes sense..lol

Hi try this
=index(A:A,match(C1,B:B,0))
B:B represents customer Number
A:A Date of purchase column
C1 is required customer number
Punnam

i can post example, but it might take a while do edit... tbh, the customer is a patients hospital number and the purchase is a sterile equipment id number.. but i wrote it the way i did to make it easier to understand.

here is an example

as the data increases patients will/may come back for more treatment so another entry for that patient will be seen, so the next date and list of tray id's will be needed. but i dont want it to show up if its the same date, will look at the file now

ive looked at your sheet, can you ammend it so that it doesnt look at the same date in the previous column. i just want 1 date with the information below it, then the next column would be another date.

i dont mind messing with VBA if there is a solution, i have used VBA on other workbooks, but dont really know how to create it.

thank you hawkeye