vlookup using 4 columns returning a 5th

[tb]

HI,

I’m trying to automate price entry by date and symbol.

I have 2 sheets, coinpricelookup and a test sheet

In my test sheet I want to match the date and symbol against the coinpricelookup table and return the price for the date.

In both sheets, column A has a date value, example 12/01/2020 and column b symbol, example btc

The value I want to return is price from coinpricelookup table column C where date and symbol match.

thanks all

I’ve spent 3 long days trying to figure this out and all I get is either err:502 or #n/a

[JohnSUN]

Hi and welcome! Sorry, but without a sample spreadsheet, it’s hard to figure out which four columns you’re talking about - in the description text, only two columns are searched in each of the two sheets. By the way, will there be no duplicates for dates and symbols? If for one day and one symbol two or more price values suddenly meet, which of the prices would you like to see?

[tb]

ok so is sheet 1 i have a list of dates and symbols, these are not unique the dates and symbols may repeat.

in sheet 2 i have a list of dates, symbols and price
these are unique where there will be only 1 entry for a symbol for a given date

what i want to do is for each date & symbol pair I want to lookup the price from sheet2 and populate a cell on sheet 1 on the same row that the date and symbol occured.

[JohnSUN]

There are a lot of different solutions. They are SUMIFS(), SUMPRODUCT() and even AVERAGEIFS()

[EarnestAl]

@tb your “answer” is not an answer, just a repeat of the incomplete question. You should have either commented, or amended the question.

Create a sample spreadsheet with a small amount of made-up data as requested, click edit below your question and then click the paperclip icon to upload your .ods

[devasuran]

Please find the steps below:
Step 1: Create a composite lookup key in the ‘coinpricelookup’ sheet
A B C D
12/01/2020 BTC 12/01/2020-BTC $ 120
Formula: =CONCATENATE(TEXT(A2,"MM/DD/YYY"),"-",B2)

Step 2: Use vlookup to search the composite key to return the price in the test sheet

    A                       B                      C                                          D
 12/01/2020          BTC                  $120

C2 Formula: =VLOOKUP(CONCATENATE(TEXT(A2,"DD/MM/YYYY"),"-",B2),$COINPRICELOOKUP.$C$2:$D$2,2,1)
^_____ Good practice: You could create a named range_____^

Please see if this works out for you.