Tables formula assistance request

Assuming sheet2 table occupies A1:K8, then you can use this formula in A3 of Sheet1:

=INDEX(Sheet2!A2:A8,MATCH(A2,INDEX(Sheet2!B2:K8,0,MATCH(A1,Sheet2!B1:K1,0)),0))

Hope this helps.

Pete

Thanks!

Works perfectly.. didnt think it would be THAT fast for a response..

What if cell A2=4.6, how would I round the formula, or would I round ahead of time in cell formatting?

reason I ask is the example is a very simplified table

the table values are double to triple digit values with no set rounding.

So cell A2 would actually have to find the closest match to its value

It would help if you attached a sample workbook - the FAQ describes how to.

You can put ROUND(A2,0) instead of just A2 in the first MATCH - this will round A2 to an integer (up or down), so it will give you the closest match.

Hope this helps.

Pete

(Not so quick this time, as I've been asleep)

If that takes care of your original question, please select Thread Tools from the menu above your first post and mark this thread as SOLVED.

Also, since you are relatively new to the forum, you might like to know that you can directly thank those who have helped you by clicking on the small "star" icon located in the lower left corner of a post that you have found to be helpful (not just in this thread - for any post that has helped you). This also adds to the reputation of the poster (the small green bars in the poster's profile).

Pete

Your example data keeps changing.

If A1 is expected to be an exact match to the data in row 1, but for A2 you want an approximate match, then you can try this:

=INDEX(Sheet2!A2:A8,MATCH(A2,INDEX(Sheet2!B2:K8,0,MATCH(A1,Sheet2!B1:K1,0))))

i.e. I've omitted the final ,0 (which forced the first MATCH function to look for an exact match). For this to work properly, though, the data needs to be sorted in increasing order, and for many of your columns that is not the case, so I'm not sure how much use it will be to you.

Pete

Thanks again Pete_UK

I mentioned the change in the example in post#4 which is why i added the visual example change in post#6


The formula in post#7 always results in "C" unless the value in the table is an exact match for the A2 value

Well, I DID say that I wasn't sure how much use that will be to you, as your data is not sorted within the columns.

You will have to use an exact match, and so you will have to manipulate the value of A2 as I suggested in Post #5, but given the variations in the table values that you have posted I'm not sure how you would accomplish this.

Pete

Thanks again Pete_UK

I'll see if the tables currently in use are arranged in order of value or just random.

Book2.xlsx Attached is workbook2.xlxs

I was able to confirm the data tables are in descending order of value across both the columns and rows

I compressed the table and function onto a single Sheet to make things easier to understand from my end.

Cell D3 contains the formula to check the table (B5:Q38) for the closest matching value of Cell D2 , Cell D1 identifies the column to use

If the data is in descending order then you will have to use a -1 instead of the original zero, like this:

=INDEX(A5:A38,MATCH(D2,INDEX(B5:Q38,0,MATCH(D1,B4:Q4,0)),-1))

This will find the smallest value that is greater than or equal to the sought item, i.e. it will match on 1732 in column B and thus give a corresponding value of 16 from column A. However, 1540 is actually closer to the sought value of 1550, so I imagine that you really want 18 to be returned in D3 rather than 16. Is this the case? If so, what happens if the sought value is exactly in the middle between two values in column B - which one to take?

Pete

Pete

You are correct, a value of 1550 should return 18 rather than 16.

As for the issue of a value exactly between two values in column B, then take the lower value in column B.


doing some reading on this during shift breaks, I found this on another site

The third and final Closest Match formula will return the value in a list that is closest to a specified value. The returned value might be less than the test value or it might be greater than the test value.

=INDEX(CMTable,MATCH(MIN(ABS(CMTable-E111)),ABS(CMTable-E111),0),1)

Thus, if cell E111 contains the value 5, the formula will return 5.1, since 5.1 is closer to 5 than any other value in the list.

I converted the above to my table;

Please Login or Register  to view this content.

which works for finding the closest matching value, but only for B5:B38.

I'm thinking the answer is somewhere between your formula and the one above..

Cheers
Steve

Well, it's 2am here, so it's time I went to bed. Maybe in the morning things will seem a little clearer.

Pete

This formula will return the best match for the value of cell D2, but only if you use a single defined column instead of finding the column from D1 value.

Please Login or Register  to view this content.

This formula will return the higher value for the value of cell D2, as well as the correct column based on the value of cell D1.

Please Login or Register  to view this content.

somewhere between is the result I need to achieve.

Best Match of cell value D2, from the column defined by cell value D1.


The second formula is very close to the result I need, but it will find the next higher value instead of the best match.

Try this formula in D3:

=IF(INDEX(INDEX(B5:Q38,0,MATCH(D1,B4:Q4,0)),MATCH(D2,INDEX(B5:Q38,0,MATCH(D1,B4:Q4,0)),-1))-D2<D2-INDEX(INDEX(B5:Q38,0,MATCH(D1,B4:Q4,0)),MATCH(D2,INDEX(B5:Q38,0,MATCH(D1,B4:Q4,0)),-1)+1),INDEX(A5:A38,MATCH(D2,INDEX(B5:Q38,0,MATCH(D1,B4:Q4,0)),-1)),INDEX(A5:A38,MATCH(D2,INDEX(B5:Q38,0,MATCH(D1,B4:Q4,0)),-1)+1))

It should do what you want.

Hope this helps.

Pete

Thank you very much Pete

much more detailed than I originally thought it would be

Here's a slightly shorter version of the same thing:

=INDEX(A5:A38,MATCH(D2,INDEX(B5:Q38,0,MATCH(D1,B4:Q4,0)),-1)+IF(INDEX(INDEX(B5:Q38,0,MATCH(D1,B4:Q4,0)),MATCH(D2,INDEX(B5:Q38,0,MATCH(D1,B4:Q4,0)),-1))-D2<D2-INDEX(INDEX(B5:Q38,0,MATCH(D1,B4:Q4,0)),MATCH(D2,INDEX(B5:Q38,0,MATCH(D1,B4:Q4,0)),-1)+1),0,1))

If that solves your problem please comply with my request at the end of Post #5.

Pete

This is what I hope to be the last formula needed.



A1 value = 45°
A2 value = 113
A3 result should be the value in Cell E10

the table below = A5:K20

B1 formula = find exact match for row B5:J5
B2 formula =INDEX(A6:A20,MATCH(MIN(ABS(A6:A20-A2)),ABS(A6:A20-A2),0)) <-- Best Match for A5:A20 column






nm 30° 35° 40° 45° 50° 55° 60° 65° 70° nm
150 10 8 6 5 4 4 3 3 3 150
140 11 8 7 6 5 4 4 3 3 140
130 12 9 7 6 5 5 4 4 3 130
120 13 10 8 6 5 5 4 4 3 120
110 14 11 8 7 6 5 4 4 4 110
100 15 12 9 8 6 6 5 4 4 100
90 17 13 10 9 7 6 5 5 5 90
80 19 15 12 10 8 7 6 6 5 80
70 22 17 13 11 9 8 7 6 6 70
60 26 19 16 13 11 9 8 7 7 60
50 31 23 19 15 13 11 10 9 8 50
40 38 29 23 19 16 14 12 11 10 40
30 51 39 31 26(23) 22(19) 19(16) 16 15 14 30
20 77 58(55) 47(43) 39(35) 32(29) 28(24) 24(21) 22(19) 20(17) 20
10 153(150) 116(110) 93(86) 77(70) 65(58) 56(48) 49(42) 45(38) 41(35) 10

Still stuck,

How do I merge the following 2 formula into a single formula?

=INDEX(B6:J6,MATCH(A1,B6:J6,0)) <-- exact match for cell value A1
=INDEX(A6:A20,MATCH(MIN(ABS(A6:A20-A2)),ABS(A6:A20-A2),0)) <-- best match for cell value A2

Cell A3 returns the cross indexed table value (A5:K20)
of column A6:A20 and row B6:J6

Book7.xlsx

Here is the actual table I'm trying to cross reference.

Slowly but surely resolved the formulas.

One final formula needed..


Cell A1 is a Drop-down list which contains "Option1", "Option2" and "Option3".

Cell A2 equals the cell value of D1 if, A1 = "Option1"
Cell A2 equals the cell value of E1 if, A1 = "Option2"
Cell A2 equals the cell value of F1 if, A1 = "Option3"

How would I create a formula for A2 that covers those 3 conditions?

Thanks Tony

I was close with =IF(A1="Option1,D1,D2)

Just could not figure out the nesting of multiple IF statements.

Cheers!

New question, same project

A1 formula; =IF(ISBLANK(E11),"N/A",IF(E11>X15,"",IF(AND(E11>=Z15,E11<=X15),"Ty","Tr")))

how would I include 'E11>X15 or E11>X16' 'E11>=Z15 or E11>=Z16' 'E11<=X15 or E11<=X16'


The result should be something like this..

=IF(ISBLANK(E11),"N/A",IF(E11>X15 or E11>X16,"",IF(AND(E11>=Z15 or E11>=X16,E11<=X15 or E11<=X16),"Ty","Tr")))

I cannot get the format correct

I removed the last formula from the sheet and went with a 2 cell process.


New question

A1="Ar"
A2="Qy"
A3="Br"
A4="DAy"

All entries will end in either a 'y' or an 'r'

A6=(=CONCATENATE(A1," ",A2," ",A3," ",A4)

without using Sort or Filter,

How would I reorder A6 to display all cells that end in 'r' BEFORE any cells that end in 'y'?

VBA is worth a try, yes

Works like a charm

Thanks Tony