If the condition is true then fetch the data.

**shivarajm** · 02-26-2012, 06:59 AM

<or = Courier N W E S
299 Surface 7 5 10 4
599 Surface 10 10 15 12
1000 Surface 15 15 30 16
299 Arrow 20 34 18 21
599 Arrow 30 44 28 35
1000 Arrow 46 54 38 46
This master Table sheet1

Courier side Weight Rate
Surface E 100
Surface W 1000
Arrow S 26533
Surface N 23231
Arrow E 255
Surface E 455
Arrow W 121
Arrow N 1500
Surface S 45
Arrow W 46
Arrow E 458
Surface S 753
Arrow W 12
Arrow N 2554
Surface W 1213
Arrow W 456
Surface S 78

Sheet 2
i want fetch the data in Rate column, if column courier and side matching in sheet1 and the weight is <= column1 of sheet1

Eg:
Courier side Weight Rate
Surface E 100 10
Surface W 1000 15

**arthurbr** · 02-26-2012, 07:21 AM

To best describe or illustrate your problem you would be better off attaching a dummy workbook, the workbook should contain the same structure and some dummy data of the same type as the type you have in your real workbook - so, if a cell contains numbers & letters in this format abc-123 then that should be reflected in the dummy workbook. Don't upload a picture when you have a workbook question. None of us is inclined to recreate your data. Upload the workbook and manually add an 'after' situation so that we can see what you expect. In addition clearly explain how you get the results..
To attach a file to your post, you need to be using the main 'New Post' or 'New Thread' page and not 'Quick Reply'.
To use the main 'New Post' page, click the 'Post Reply' button in the relevant thread.

On this page, below the message box, you will find a button labelled 'Manage Attachments'.
Clicking this button will open a new window for uploading attachments.

You can upload an attachment either from your computer or from another URL by using the appropriate box on this page.
Alternatively you can click the Attachment Icon to open this page.

To upload a file from your computer, click the 'Browse' button and locate the file.

To upload a file from another URL, enter the full URL for the file in the second box on this page.
Once you have completed one of the boxes, click 'Upload'.

Once the upload is completed the file name will appear below the input boxes in this window.
You can then close the window to return to the new post screen.

**marreco** · 02-26-2012, 07:24 AM

Try...

IFERRO = (INDEX (Plan1! $ B $ 2: $ F $ 10, MATCH (Plan1! B2, Sheet1! $ B $ 2: $ B $ 14, 0), 3);" ")

**shivarajm** · 02-26-2012, 07:36 AM

its not working as my req'ment

**marreco** · 02-26-2012, 08:15 AM

Try
=SOMARPRODUTO((Sheet1!$B$2:$B$7=A2)*(Sheet1!$A$2:$A$7<=C2)*Sheet1!$E$2:$E$7)

**shivarajm** · 02-26-2012, 08:52 AM

Its not i am asking, Take sheet1 as master table and fetch the data to sheet2 if the condition is true
eg:
if courier=surface and side=N and weight<=299 then rate=7 like this..

**Marcol** · 02-26-2012, 09:07 AM

With your "Rate Bands" changed to >= rather than <=

Make two Dependant Named Ranges, this will allow you to add different carriage methods, say "Air Mail", and each method can have a varying number of rate bands. (Total rows for all methods = 48, in this workbook)

1/. DeliveryBands
Refers to:=

=INDEX(Sheet1!$A$2:$A$50,MATCH(Sheet2!$A2,Sheet1!$B$2:$B$50,0),1):INDEX(Sheet1!$A$2:$A$50,MATCH(Sheet2!$A2,Sheet1!$B$2:$B$50,0)+COUNTIF(Sheet1!$B:$B,Sheet2!$A2)-1,1)

2/. DeliveryRates
Refers to:=

=INDEX(Sheet1!$C$2:$F$50,MATCH(Sheet2!$A2,Sheet1!$B$2:$B$50,0),MATCH(Sheet2!$B2,Sheet1!$C$1:$F$1,0)):INDEX(Sheet1!$C$2:$F$50,MATCH(Sheet2!$A2,Sheet1!$B$2:$B$50,0)+COUNTIF(Sheet1!$B:$B,Sheet2!$A2)-1,MATCH(Sheet2!$B2,Sheet1!$C$1:$F$1,0))

Then in Sheet2 D2

=LOOKUP(C2,DeliveryBands,DeliveryRates)

Drag/Fill Down

**marreco** · 02-26-2012, 09:18 AM

in your language

INDEX (Sheet1! $ C $ 2: $ F $ 7, MATCH (A2, Sheet1! $ B $ 2: $ B $ 7, 0), MATCH (B2, Sheet1! $ C $ 1: $ F $ 1, 0))

**shivarajm** · 02-26-2012, 10:22 AM

I would like you to explain in more detail for me the named ranges:
DeliveryRates
DeliveryBands

I'd like to understand this brilliant idea
Could you explain? please

**marreco** · 02-26-2012, 09:15 AM

Try
=ÍNDICE(Sheet1!$C$2:$F$7;CORRESP(A2;Sheet1!$B$2:$B$7;0);CORRESP(B2;Sheet1!$C$1:$F$1;0))

**Marcol** · 02-26-2012, 09:46 AM

@ marreco the task is to return the value from part of the lookup table based on "Courier" & "side" where the "weight" falls into a band width.

See Post #7 (it can be simplified if the ranges are static)

**marreco** · 02-26-2012, 09:53 AM

I would like you to explain in more detail for me the named ranges:
DeliveryRates
DeliveryBands

I'd like to understand this brilliant idea
Could you explain?

**marreco** · 02-26-2012, 09:48 AM

I'm sorry I going crazy

**Marcol** · 02-26-2012, 11:39 AM

I'll try

The basis of the name is to dynamically select a range using
INDEX(range,row number,column number)

So with "DeliveryBands"
The starting cell is
INDEX(Sheet1!$A$2:$A$50,MATCH(Sheet2!$A2,Sheet1!$B$2:$B$50,0),1)
The ending cell is
INDEX(Sheet1!$A$2:$A$50,MATCH(Sheet2!$A2,Sheet1!$B$2:$B$50,0)+COUNTIF(Sheet1!$B:$B,Sheet2!$A2)-1,1)

MATCH(Sheet2!$A2,Sheet1!$B$2:$B$50,0) returns the row number in Sheet1!$B$2:$B$50 for the "Courier" specified in Sheet2!$A2
By adding the number of times the "Courier" appears in Sheet1!$B:$B minus one we get the row number of the last time the "Courier" occurs in Sheet1!$B$2:$B$50.

As there is only one column in range Sheet1!$A$2:$A$50 the column number is 1

We then join the two parts with the : (colon) operator to get the whole range
"Starting Cell" : "Ending Cell"

Similarly with "DeliveryRates"
The starting cell is
INDEX(Sheet1!$C$2:$F$50,MATCH(Sheet2!$A2,Sheet1!$B$2:$B$50,0),MATCH(Sheet2!$B2,Sheet1!$C$1:$F$1,0))
The ending cell is
INDEX(Sheet1!$C$2:$F$50,MATCH(Sheet2!$A2,Sheet1!$B$2:$B$50,0)+COUNTIF(Sheet1!$B:$B,Sheet2!$A2)-1,MATCH(Sheet2!$B2,Sheet1!$C$1:$F$1,0))

This time the range Sheet1!$C$2:$F$50 has more than one column so we have to specify the Column we need
MATCH(Sheet2!$B2,Sheet1!$C$1:$F$1,0) returns the column number for the "side" specified in Sheet2!$B2 matched in the range Sheet1!$C$1:$F$1

Perhaps the best way to understand the formula is to copy it from the Names Manager into a spare cell then use the formula builder (Put the curser in the formula bar then press fx)
Highlight each part in turn to see how it is built.
Then step through in Tools > Formula Auditing > Evaluate formula to see the returned values progress.
Also do this with the formula in Sheet2 Column D

**marreco** · 02-26-2012, 11:46 AM

Thank you master!

If the condition is true then fetch the data.

LinkBack

Thread Tools

Rate This Thread

Display

Hybrid View

If the condition is true then fetch the data.

Re: If the condition is true then fetch the data.

Re: If the condition is true then fetch the data.

Re: If the condition is true then fetch the data.

Re: If the condition is true then fetch the data.

Re: If the condition is true then fetch the data.

Re: If the condition is true then fetch the data.

Re: If the condition is true then fetch the data.

Re: If the condition is true then fetch the data.

Re: If the condition is true then fetch the data.

Re: If the condition is true then fetch the data.

Re: If the condition is true then fetch the data.

Re: If the condition is true then fetch the data.

Re: If the condition is true then fetch the data.

Re: If the condition is true then fetch the data.

Thread Information

Users Browsing this Thread

Tags for this Thread

Bookmarks

Bookmarks

Posting Permissions