CODEWITHSUNDEEP
excelexcel-formula
Kyle Johal-Hansen
Kyle Johal-Hansen

Reputation: 1

How can I use the result of a formula in another cell and pull the data 1 cell to the right of the original search data

I am using Max(Index to find the data in a table with the search query being the date. i also need the data 1 cell to the right of the date column.

I have tried Offset(Max(Index but that just yielded a reference error. i have tried searching online but have found no other way of doing this. What i expected to happen was the data 1 cell to the right of the search query was found and pulled to the spreadsheet. below is the formula used that did not work

=OFFSET(MAX(INDEX((D16='Sheet1'!B$2:B$7500)*'Sheet1'!C$2:C$7500,)),0,1)

attached is some sample data from sheet 1

search query date cost
100336288 11 July 2023 -£200.00
100025143 11 July 2023 -£50.00
100301369 11 July 2023 -£5,771.40
100280645 11 July 2023 -£500.00
100138224 11 July 2023 -£50.00
100336288 30 June 2023 -£3,000.00
100336288 26 June 2023 -£999.51
100280645 20 June 2023 -£167.36
100280645 20 June 2023 -£332.64
100025143 14 June 2023 -£50.00
100138224 12 June 2023 -£50.00
100336288 07 June 2023 -£200.00
100336288 06 June 2023 -£700.00
100336288 02 June 2023 -£1,500.00
100301369 02 June 2023 -£3,000.00
100138224 12 May 2023 -£50.00
100336288 09 May 2023 -£200.00
100025143 08 May 2023 -£50.00
100336288 04 May 2023 -£700.00
100336288 13 April 2023 -£200.00
100025143 12 April 2023 -£50.00
100336288 04 April 2023 -£700.00
100336288 13 March 2023 -£804.44
100025143 11 March 2023 -£50.00
100336288 07 March 2023 -£700.00
100336288 06 March 2023 -£200.00
100025143 10 February 2023 -£50.00
100336288 06 February 2023 -£200.00
100336288 06 February 2023 -£982.00
100336288 06 February 2023 -£700.00
100025143 17 January 2023 -£50.00
100336288 09 January 2023 -£200.00
100025143 06 January 2023 -£50.00
100336288 05 January 2023 -£982.00

sheet 2 sample data i have removed the = to paste it here.

search query Date
100025143 MAX(INDEX((A2=Sheet1!A$1:A$7335)*Sheet1!B$1:B$7335,))
100138224 MAX(INDEX((A3=Sheet1!A$1:A$7335)*Sheet1!B$1:B$7335,))
100280645 MAX(INDEX((A4=Sheet1!A$1:A$7335)*Sheet1!B$1:B$7335,))
100301369 MAX(INDEX((A5=Sheet1!A$1:A$7335)*Sheet1!B$1:B$7335,))
100336288 MAX(INDEX((A6=Sheet1!A$1:A$7335)*Sheet1!B$1:B$7335,))
100283819 MAX(INDEX((A7=Sheet1!A$1:A$7335)*Sheet1!B$1:B$7335,))
100281696 MAX(INDEX((A8=Sheet1!A$1:A$7335)*Sheet1!B$1:B$7335,))
100149591 MAX(INDEX((A9=Sheet1!A$1:A$7335)*Sheet1!B$1:B$7335,))
100099054 MAX(INDEX((A10=Sheet1!A$1:A$7335)*Sheet1!B$1:B$7335,))

Upvotes: 0

Views: 66

Answers (1)

BeXXsoR
BeXXsoR

Reputation: 114

VLOOKUP does exactly this - it looks for a value in the first column of a matrix, and when found one, it returns the value of a cell in a later column in the same line. The syntax goes:

VLOOKUP(lookup_value, table_array, col_index_num, [range_lookup])

where

  • lookup_value is the value to look for (the search query in your example)
  • table_array is the table/matrix containing all your data (or at least the relevant parts).
  • col_index_num is the number of the target column w.r.t. this matrix
  • range_lookup is False if you want to look for exact matches of your lookup_value and True if you want to look for approximate values of it.

Keep in mind that

  1. the lookup_value has to be in the first column of the table_array, and
  2. the table is searched from top to bottom and stops after the first fit, meaning that if you have duplicates of lookup_value in your table, VLOOKUP will only find the first one.

p.s.: In the newer excel versions you also have XLOOKUP as an improvement/ enhancement over VLOOKUP.

Upvotes: 0

Related Questions