if, and to extact item from table or not. {SOLVED}

**simpson** · 11-18-2011, 11:45 AM

Can someone please help me.
I need a formula for cell D2 that will return the mileage value from sheet 1 by comparing it to the information from sheet 1.
ie. if (location = bobs) + (term = HLN) miles = 167
heres the catch,
If (truck=cc) miles=0

**NBVC** · 11-18-2011, 12:13 PM

Try:

=IF(B2="cc",0,INDEX(Sheet1!$B$4:$D$7,MATCH(A2,Sheet1!$A$4:$A$7,0),MATCH(C2,Sheet1!$B$3:$D$3,0)))

copied down

**simpson** · 11-18-2011, 12:26 PM

that works! you are awsome.
thanks again!

**Marcol** · 11-18-2011, 12:43 PM

FWIW
Once again I charged down the dynamic names route and arrived late!
I'll post it anyhow as it's done.

This table can be expanded with as many rows and columns as you need.

**NBVC** · 11-18-2011, 12:47 PM

Does every question require dynamic range solutions?

**Marcol** · 11-18-2011, 01:03 PM

@ NBVC
Not really, but several posts recently have been about this type of question.
The actual worksheets have ended up many times larger than the original sample.
The solutions end up with volatile dynamic ranges.
Then the OP complains the worksheets is painfully slow.

Guess I've just got a bee in my bunnet!

**Whizbang** · 11-18-2011, 01:29 PM

I am with you Marcol. Dynamic Ranges are a quick and easy way to optimize your workbook. Especially if the person who will ultimately be in charge of using or maintaining the file is not an Excel expert. The speed difference between a dynamic range and just referencing a large range like A1:A100000 isn't all that much, and not usually noticiable, but it prevents the need to maintain it in the far future when you've forgotten how the workbook is organized. And the self documenting bonus of naming your range is a huge plus.

Plus I just like the coolness of the INDEX approach for dynamic ranges.

**NBVC** · 11-18-2011, 01:37 PM

I agree but it isn't always necessary, not all spreadsheets are going to use ranges into the 1000's of rows....

**simpson** · 11-18-2011, 01:54 PM

If this helps.....
the worksheet i will be applying the formula to is very different then the sample. It will still take some time to imput the formula and make it work.
The solution worked in my sample great, but when I transfered it to the actual sheet and modified it I am coming up with the location name from column A instead of the mileage from column D.
I'll keep plugging away though.
I'de like to show you guys the actual workbook sometime. I've been told it is quite impressive. but it is slow to open. It really should be an access database but i'm running into a wall getting started on that progess.

thanks to all!

**NBVC** · 11-18-2011, 01:59 PM

Here is a good website that teaches you the Index/Match combination:

http://www.contextures.com/xlfunctions03.html

It also shows you how to create dynamic named ranges, if you need them:

http://www.contextures.com/xlnames01.html#Dynamic

**simpson** · 11-18-2011, 02:09 PM

here is a better sample of the actual. If your interested.

**NBVC** · 11-18-2011, 02:13 PM

Try:

=IF(C2="cc",0,INDEX(Sheet1!$B$4:$F$14,MATCH(A3,Sheet1!$A$4:$A$14,0),MATCH(Q3,Sheet1!$B$3:$F$3,0)))

**Marcol** · 11-18-2011, 02:19 PM

@ Whizbang, forget the "Coolness"

Plus I just like the coolness of the INDEX approach for dynamic ranges

The fact that INDEX(MATCH()) isn't volatile makes a large workbook very workable when volatile functions like OFFSET() grind to a halt.

The penalty to pay is that the workbook is slower to open because INDEX(MATCH()) is "quasi-volatile" at this stage only, but what's that compared to a sheet that opens in a flash then dies when you enter anything?

**simpson** · 11-18-2011, 02:19 PM

Thats crazy!
You must be able to figure that out faster then I can even type the question.

**Marcol** · 11-18-2011, 02:51 PM

For the record only, here it is with your data applied.

Add to Sheet1 ColumnA as required, add more Columns for "Terms"
Select from the dropdowns in Sheet" Columns A and Q

Thread: if, and to extact item from table or not. {SOLVED}

Thread Tools

Display

if, and to extact item from table or not. {SOLVED}

Re: if, and

Re: if, and

Re: if, and

Re: if, and

Re: if, and

Re: if, and

Re: if, and

Re: if, and

Re: if, and

Re: if, and to extact item from table or not.

Re: if, and to extact item from table or not.

Re: if, and to extact item from table or not.

Re: if, and to extact item from table or not.

Re: if, and to extact item from table or not.

Thread Information

Users Browsing this Thread

Bookmarks

Bookmarks

Posting Permissions