How to query a matrix for multiple values and receive value

0 votes

I have two values

  • Length
  • Weight

I am looking for a solution to the problem below:

Using the input values for length and weight, locate the next higher value for each in the matrix, and then take the value from the value column. Additionally, I want to create a fallback and always use the value 1.0 if there is no upper value for my input.

How to solve this?

I have a matrix that has these values inside:

enter image description here

Feb 11, 2023 in Others by narikkadan
• 63,420 points
330 views

1 answer to this question.

0 votes

Try:

=IFERROR(INDEX(SORT(SORT(FILTER(MATRIX, (LENGTHS>length)*(WEIGHTS>weight)),1,1),2,1),1,3),1)

, where MATRIX, LENGTHS, and WEIGHTS refer to the data on the right, whilst length and weight refer to the cells containing 80 and 450.

Just saw you said H6 was what I referred to as length - if so:

=IFERROR(INDEX(SORT(SORT(FILTER(K6:M11, (K6:K11>H6)*(L6:L11>H7)),1,1),2,1),1,3),1)

in H8.

answered Feb 11, 2023 by Kithuzzz
• 38,010 points

Related Questions In Others

0 votes
1 answer

How to unmerge multiple cells and transpose each value into a new column in Pandas dataframe from excel file

Try this: df = pd.read_excel("Sample_File.xlsx", header=[0,1,2,3,4,5], index_col = ...READ MORE

answered Jan 8, 2023 in Others by narikkadan
• 63,420 points
1,868 views
0 votes
0 answers

How is it possible for DynamoDB to support both Key-Value and Document database properties at the same time

As per DynamoDB's documentation, it supports both ...READ MORE

Apr 5, 2022 in Others by Kichu
• 19,050 points
602 views
0 votes
1 answer
0 votes
1 answer

How to add Conditional Formatting in Excel for a Range of Values

Three distinct rules are required, one for ...READ MORE

answered Oct 24, 2022 in Others by narikkadan
• 63,420 points
804 views
0 votes
1 answer

How can I use a command button in excel to set the value of multiple cells in one click?

Try this: Private Scan As Integer Private Sub CommandButton1_Click() ...READ MORE

answered Oct 24, 2022 in Others by narikkadan
• 63,420 points
535 views
0 votes
1 answer

How can a column be set to display multiple spaces and tabs (white space) in a cell?

By entering your "code" in Word and ...READ MORE

answered Jan 19, 2023 in Others by narikkadan
• 63,420 points
309 views
0 votes
1 answer
0 votes
1 answer

How to automatically assign a color to the maximum and minimum values in a set of selected cells in Excel?

See Conditional Formatting, which may be accessed ...READ MORE

answered Apr 7, 2023 in Others by Kithuzzz
• 38,010 points
310 views
0 votes
1 answer

How to rename a workbook other than using (Name) and (FileSystemObject. MoveFile)?

Use  SHFileOperation API Option Explicit Private Declare PtrSafe Function SHFileOperation ...READ MORE

answered Feb 11, 2023 in Others by Kithuzzz
• 38,010 points
202 views
webinar REGISTER FOR FREE WEBINAR X
REGISTER NOW
webinar_success Thank you for registering Join Edureka Meetup community for 100+ Free Webinars each month JOIN MEETUP GROUP