Formula Help Needed

excel_training_wheels said:

Hi--

I have three main columns of data. Column A includes city names. Column B includes hotel names. Column C includes random numbers. I am trying to have excel return the number associated with the second occurance of a given hotel in each of my cities.

Boston Days Inn 1
Boston Hyatt 2
Boston Days Inn 3
Boston Hilton 4

I want the formula to return the number *3* associated with the second Days Inn, within Boston.

So I inserted a column C (at the advice of a fellow board member who helped me yesterday!!) that connects Boston with Days Inn (=A2&B2). I also put the city name in F2 and hotel name in G2.

The formula in H2:
=VLOOKUP(F2&G2,C1:D11,2,0)

This returns *1* for the first occurance of Days Inn in Boston, which is great. However, now I want to adjust so that *3* is returned, for the second occurance. I can not re-sort the data...it has to stay as is.

Thanks for your help!!!!!

Click to expand...

Peter_SSs said:

excel_training_wheels

Just for the fun of it, here's a way without the array formula, but still with the extra column like my answer to your previous thread.

The formula in C2 (copied down) is now:
=A2&B2&SUMPRODUCT(--(A$2:A2=A2),--(B$2:B2=B2))

The formula in I1 is:
=F2 & " "&G2&" Occurrence "&H2

The formula in I2 is now:
=VLOOKUP(F2&G2&H2,C1:D11,2,0)
...

Click to expand...