Forum Discussion
I need a formula for this
Use
=INDEX(C2:C124,MATCH(J10,A2:A124))
or
=VLOOKUP(J10, A2:C124, 3)
1) We look at the lower bound of each tier instead of the upper bound, i.e. column A instead of column B.
2) The range in column C must have the same size as the range in column A.
3) Removing 0 as third argument of MATCH allows for an approximate match instead of an exact match.
see attached
I am trying to get the matching value from the table in sheet 2 into sheet1 ''cell n4'' based on "cell j1" and "cell M4"
Since column A on Sheet2 is formatted as Text, cell M4 on Sheet1 should be formatted as Text too.
The formula in N4 is
=INDEX(Sheet2!B3:R88,MATCH(M4,Sheet2!A3:A88,0),MATCH(J1,Sheet2!B2:R2,0))
See the attached version.
I want to do this index match formula
however, I want it should check for the answer from two tables
see attached
The first thing I noticed was that I2:I88 and R2:R88 on Sheet2 are text instead of numbers,
Select I2:I88.
On the Data tab of the ribbon, click Text to Columns, then click Finish.
Do the same for R2:R88.
Furthermore, the codes in A2:A88 are text while those in A92:A126 are numbers, so we have to treat them separately. See the attached version.
- Its working Thx