0

I have a really simple question for someone that uses excel regularly...

I have a data (Data) sheet and a report generation sheet (Report).

I'd like to grab a cell value from the data sheet and place it in the formula cell on the report generation sheet.

I'd like a formula that says basically:

If Data!Date = A5 & Data!Channel = "Online" & 
Data!Score = "400" then return Data!Decision

Data!Decision could be yes, or no, or a number. Basically, I just want to reutrn the coresponding cell for a given Data, Channel, and Score...

Any help?

Thanks in advance!

EDIT to model the tables for better help and suggestions:

Data Table:

Date Channel Score Decision
9/9/14 Online 400  7
10/2/14 TV    400  5

Report Table (this table is only for Online and Score = 400)

Date   Decision
9/9/14 

I'd like the value of 7 to be returned in that empty cell in the Report Table, thus the formula should take into account Channel = "Online" and Score = 400

  • We need to know what the dataset looks like for the data table. – wbeard52 Oct 28 '14 at 18:26
  • What does this have to do with vlookup? I don't understand your data structure. – Raystafarian Oct 28 '14 at 18:27
  • Updated to reflect data structure... VLOOKUP should match values in the Data! table to Channel = "Online" and Score = 400 – gh0strider18 Oct 28 '14 at 18:32
  • Can there be only one record for a given date, and if the channel and score on that date match your criteria then you post the decision value, or, is this like a database, where a date can appear multiple times, with varying combinations of channel and score and you want to retrieve the decision value if you find a record that matches all criteria? – fixer1234 Oct 29 '14 at 3:32
  • ghOstrider18 - If you can get back to me on the question in my comment, I might have a solution for you. – fixer1234 Oct 29 '14 at 19:46
1

I would do this with the following array function: (the example assumes your tables start at A1 in each sheet)

=INDEX(Data!D2:D3,MATCH(1,(Data!A2:A3=Report!A2)*(Data!B2:B3="Online")*(Data!C2:C3=400),0))

Because this is an array function, you need to press CTRL+SHIFT+ENTER when inputting it.

0

One way to accomplish this is to make yourself a helper column that is a concatenation of the two keys you're trying to look up. Likewise you'll have to have another concatenated column in your lookup table, as well. If I recall correctly, you will then have to copy and paste values in order to get the lookup to work. I tried it on a music database, and I believe Excel can't use the results of a formula as a lookup key. Good luck.

  • Can you expand on this, maybe a simple mockup to show what it would look like in this example? – fixer1234 Oct 28 '14 at 21:04
  • Yes, a simple example would really help in this instance. So, based on the model data I presented in the original question, how could we accomplish this? – gh0strider18 Oct 28 '14 at 23:18
  • In your example the Channel and Score columns look like they're B and C. Insert a column before B, thus making Channel and Score now C and D. Concatenate column B using =C2 & D2, then insert new column, copy and paste values into new column, then delete concatenated column. Now use =vlookup(B2,Lookuptable,4,false) to get the value of 7 from the Decision column. If I'm not understanding the question correctly, I apologize. You're going to need more than just the Date to get your vlookup to work, though. – BillDOe Oct 29 '14 at 0:31
0

Here is one approach, which is similar to BillOer's use of a helper column. On the Data sheet, insert a column to the left of Date. Let's say the new column is A, and Date, Channel, Score, and Decision are columns B through E. Say the data is in rows 2 through 1000. In A2, enter:

    =B2&C2&D2

Copy that down column A for all rows.

In your Report sheet, assume the cell you show with the date is A5, as you describe. Say A2 holds the Channel target value ("Online" in your example), and B2 holds the Score target value ("400" in your example). The formula in B5 for the returned decision is:

 = VLOOKUP(A5&A$2&B$2,Data!A$1:E$1000,5,false)

Copy and paste as needed in column B.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.