Search code examples
excelexcel-2013

How to have Excel return multiple cells of data from one search

I have two sheets in Excel.

The first sheet is called 'Car Search'.

The second sheet is called 'Raw Data'. This sheet contains a 'Car ID' column for each car variant (e.g. VW Golf TDI has the Car ID 2612. VW Golf TSI has the Car ID 2613).

I want to search for the Car ID in the 'Car Search' sheet and would like it to return all the data about that specific car from the 'Raw Data' sheet.

I have tried using VLOOKUP but I don't know how it would be able to return data from multiple cells and columns.

Please see screenshot of both sheets below.

Car Search

Raw Data

Any help will be much appreciated!

EDIT:

I have one more issue: I have tried to use the same formula in the Notes field but it only picks up the values from the first 'Notes' Column in the 'Raw Data' sheet. The formula is =INDEX('Raw Data'!A:Z,MATCH($B$3,'Raw Data'!A:A,0),MATCH(E2,'Raw Data'!1:1,0))

Car Search 2 Raw Data 2

Solution

  • You can use Index / Match to solve your problem. Here is some pseudo code to your problem that you can adapt:

    =INDEX(RawData;MATCH(Car_ID;FirstColumn_RawData;0);MATCH(DataField;FirstRow_RawData;0)

    The first match function is used to find the row where the data you want from CarID resides, and the second match is used to find the column in which the data field you want to return is located.

    Alternatively as has been suggested in another answer, simply use VLOOKUP multiple times.