Idex match or vlookup in combination of concatenate date cells with text?

[EvoBob]

Hi,

Here is an example of my excel file:
Excel example.xlsx

I have a problem regarding multiple search values which consist of date and text and duplicates.

I would like to get information (right bid/ask for the right currency pair at the right time) from sheet 2 into sheet 1. So i need to find the matching date and pair (for instance bid DEM/CHF 1/26/1997 17:49:24) from sheet 2 and bring it into sheet 1.

In sheet 1 I have a time table for every second which means that if there is no observation matching in sheet 2 I will use the last bid/ask quote for the respective currency pair.



I have tried index match formula and vlookup by concatenate the date cells with the pair without success.

Does anyone have a good tip for how I can get the information into sheet 1?

This is just an small example, my sheet 2 has 250K rows with around 8 currency pairs in total.


Thank you so much for your time and help!

Let me know if you need some more information.

And sorry for my english, im native norwegian

[FDibbins]

Please do not upload a picture of your file...rather, upload a sample of your workbook, showing what data you are working with, a few samples of your expected outcome is (manually entered is ok) and how you arrived at that. (exclude sensitive info). Pictures are pretty much impossible to edit, and no-one wants to re-type your data for you

without seeing what you have, you can maybe do a combine of the bits that you need, in a helper column, and then base the index/match on that?
To attach a file to your post,
click advanced (next to quick post),
scroll down until you see "manage file",
click that and select "add files" (top right corner).
click "select files" find your file, click "open" click "upload" click 'done" bottom right. click "submit reply"

Once the upload is completed the file name will appear below the input boxes in this window.
You can then close the window to return to the new post screen.

[EvoBob]

Hi,

Thanks for the information.

I have now removed the picture and added an excel example file instead.

[Ace_XL]

In B3 use this array formula

=IFERROR(INDEX(Sheet2!C:C,MATCH(1,(Sheet2!B:B=B$1)*(Sheet2!A:A=$A3),0)),"")

Confirm with Ctrl+Shift+Enter

Also there are 2 spaces in between the date and time of your data. Delete 1 space in order for this to work correctly

[EvoBob]

Hi,

Thanks for your reply.

When I post your formula in B3 I get an error which says #NAME? ?

Not sure I understand what you mean regarding the 2 spaces.

The problem is that my raw data lies in sheet 2, and the timeframe there is not similar to sheet 1 where I have every second from a start date to an end date. So if there is not a match in date and time from sheet 2, I have to use the last bid/ask quote if you know what I mean.

I basically need to fill in data for every second in sheet 1, and if there's no bid/ask quote for that specific second in sheet 2 I need to use last bid/ask quote.

Thanks for your help

[EvoBob]

Sry my mistake regarding the formula forgot that I have norwegian excel

Will try it out

[EvoBob]

I have now translated the formula to Norwegian and it still does not work. Only number I get is 3.3610 in cell B4, which is not the right time (its 3 seconds before sheet2), and the rest of the cells are blank.

[Ace_XL]

Works well for me wheerever there are matches. See attached

[EvoBob]

Thanks my mistake, when I made the example I meant to have the date and time in sheet 2 in increasing order which I see they are not, but it worked now thanks!

My last problem is the blank cells.

The problem is that my raw data lies in sheet 2, and the timeframe there is not similar to sheet 1 where I have every second from a start date to an end date. So if there is not a match in date and time from sheet 2, I have to use the last bid/ask quote if you know what I mean.

I basically need to fill in data for every second in sheet 1, and if there's no bid/ask quote for that specific second in sheet 2 I need to use last bid/ask quote.

Thanks for your help

[EvoBob]

Hi,

Thanks again for your answer. Do you know how to make it go faster when you copy the formula down 250K rows (it takes forever my computer almost crashes)? I heard that match/index formula is supposed to be faster than the vlookup but to me it seems to be slower.

Btw do you know a easy way of deleting the 1 space between the date and time for 250K rows?

Thanks

[Ace_XL]

Instead of full column ranges, use only your data range.
e.g instaed of B:B use B1:B1000

Btw do you know a easy way of deleting the 1 space between the date and time for 250K rows?

- Select the relevant column
- Find/Replace (Ctrl+H)
- Find field: space space
- Replace field: space
- Replace all

[EvoBob]

Hi again,

Thanks yeah it may have reduced the time but still it takes forever but that's probably something I have to accept

My last problem is the blank cells. I basically need to fill in data for every second in sheet 1, so if there's no bid/ask quote for that specific second in sheet 2 I need to use last bid/ask quote.

Thanks again for your help