Offset, Match adjust previous formula

**quaney** · 02-13-2017, 09:53 AM

Hello Excel Forums,

I need to compare a value from one table to another table to determine a final value, but the formula needs to identify which range of numbers the first values belongs to.

I had a great formula provided for me for a problem with an extra variable but I'm unable to shorten it without making a mess of it, you can see the original I was provided here.

=INDEX(Result,MATCH(MIN($F2,4),Data_1,0)+MATCH($G2,OFFSET($L$2,MATCH(MIN(F2,4),Data_1,0),0,COUNTIF(Data_1,MIN($F2,4))),1)-1)

I've highlighted the parts of the formula that are relevant to what I still need. I no longer need the part that takes column F into account.

Value to be looked up is on tab 1 column F

Then the values to be used in the sorting are on tab 4, column D.

The result to be returned is found on tab 4, column E

The formula itself needs to be on tab 5, column B

My explanation is probably terrible sorry, hopefully a look at the workbook will make it clear what I'm trying to do.

**JohnTopley** · 02-13-2017, 09:55 AM

No workbook attached.

Attach a sample workbook. Make sure there is just enough data to demonstrate your need. Include a BEFORE sheet and an AFTER sheet in the workbook if needed to show the process you're trying to complete or automate. Make sure your desired results are shown, mock them up manually if necessary.

Remember to desensitize the data.

Click on GO ADVANCED and then scroll down to Manage Attachments to open the upload window.

**quaney** · 02-13-2017, 10:12 AM

Hi John,

Working on it, I had a large data set pasted in a lots of different formula everywhere, workbook was 21mb so been trying to downsize it. Will upload when fully reduced.

**quaney** · 02-13-2017, 11:02 AM

File now attached.

**davsth** · 02-13-2017, 11:07 AM

whilst a workbook is attached, there are few formulas in it or reference to cells the formula appeared in

**quaney** · 02-13-2017, 11:20 AM

This is a new workbook, the formula I posted was from a previous workbook I had help putting together. I had to remove the vast majority of my work so far to bring the workbook down to the 1MB limit. I left some notes on tab 4 and all columns containing the data to be utilised are highlighted.

The formula I gave above was what was provided last time but as this is a new sheet with different parameters the cell references will change and so will the structure as it will be using one less variable.

I can try to re write my problem if it's not clear.

**JohnTopley** · 02-13-2017, 12:34 PM

I admit I have no idea how you arrive at the answer of 11 for Sample 1:

You are comparing 1875 against column D and somehow arrive at 11

**quaney** · 02-14-2017, 12:58 PM

Haha I just wrote a random selection of numbers, I was just illustrating that the numbers were to go there, sorry should of made the effort to put the correct ones in to avoid confusion.

**quaney** · 02-14-2017, 01:03 PM

I've changed the numbers to reflect what the formula should return. Sorry

**JohnTopley** · 02-14-2017, 01:37 PM

If column A in "Tab 1" and "Tab 5" are in the same sequence then

in D1

=VLOOKUP('Tab 1'!$F2,'Tab 4'!$D$3:$E$20,2,1)

If sequence is different ..

=VLOOKUP(VLOOKUP($A1,'Tab 1'!$A$2:$G$1000,6,0),'Tab 4'!$D$3:$E$20,2,1)

OR better

add column to "Tab 1" to have the result the ..

in H1

=VLOOKUP($F2,'Tab 4'!$D$3:$E$20,2,1)

NOTE: the DIB values must be in ascending order

**quaney** · 02-15-2017, 12:27 PM

I'll admit I didn't know using the TRUE method in VLookup would be able to do this, this does exactly what I need (or seems to from the quick test I've done.)

Thanks very much!

Offset, Match adjust previous formula

LinkBack

Thread Tools

Rate This Thread

Display

Offset, Match adjust previous formula

Re: Offset, Match adjust previous formula

Re: Offset, Match adjust previous formula

Re: Offset, Match adjust previous formula

Re: Offset, Match adjust previous formula

Re: Offset, Match adjust previous formula

Re: Offset, Match adjust previous formula

Re: Offset, Match adjust previous formula

Re: Offset, Match adjust previous formula

Re: Offset, Match adjust previous formula

Re: Offset, Match adjust previous formula

Thread Information

Users Browsing this Thread

Similar Threads

[SOLVED] Index Match Formula that will adjust automatically when new columns and rows are added

[SOLVED] index match formula won't fill across columns or adjust if column is deleted

Need to add iftext in relative cell refrence to a offset(match+match) formula

Formula to adjust a weekend date to the previous Friday...

MATCH previous occurrence of text in column, use OFFSET to get input variable

Adjust OFFSET range

IF and OFFSET: how to OFFSET only if cell has a value in it (follows previous post)

Bookmarks

Bookmarks

Posting Permissions