adding to a formula, can I do it

[brianaclark1967]

Hi Guys,

I've attached a sample sheet

I have a cell that is pulling results from another sheet.

I want to add to the formula so if the cell says 1 of 3 certain keywords it pulls results from cells in the same row, same sheet.

Hope this makes sense, but the sheet should explain it

Regards

Brian

 

[Khalid NGO]

Hi Brian,
Not clear to me
Can you specify more?

 

[Khalid NGO]

My guess would be a lookup table:
=LOOKUP(D9,N7: P7,N9: P9)

But without complete information, not getting anything:
Sheet Transport! ?
Sheet Deliveries! ?

Regards,

 

[Hui]

in addition to what Khalid said above, I think the formula in D9 has an error:
=IFERROR(INDEX(Transport!G:G,MATCH(Deliveries!A:A,Transport!E:E,0)), 20)

I think should be
=IFERROR(INDEX(Transport!G:G,MATCH(Deliveries!A1,Transport!E:E,0)), 20)
or some other reference


I would also suggest that you limit the ranges

So instead of:
=IFERROR(INDEX(Transport!G:G,MATCH(Deliveries!A1,Transport!E:E,0)), 20)

Use:
=IFERROR(INDEX(Transport!G1:G1000,MATCH(Deliveries!A1,Transport!E1:E1000,0)), 20)

That is 1000th the size and so will calculate a whole lot faster

 

[brianaclark1967]

Hi Brian,
Not clear to me
Can you specify more?

Hi Khalid,

Sorry if I was unclear.

The formula in column D works fine with the sheets it's pulling information from.

I was hoping to add a formula, even if it's using conditional formatting.

If the cell in column D is over typed with Palletways, I want the result from column N (same row) to appear.

If the cell in column D is over typed with Beacon, I want the result from column O (same row) to appear.

If the cell in column D is over typed with Lowes, I want the result from column P (same row) to appear.

Hope this helps

Regards

Brian

 

[brianaclark1967]

in addition to what Khalid said above, I think the formula in D9 has an error:
=IFERROR(INDEX(Transport!G:G,MATCH(Deliveries!A:A,Transport!E:E,0)), 20)

I think should be
=IFERROR(INDEX(Transport!G:G,MATCH(Deliveries!A1,Transport!E:E,0)), 20)
or some other reference


I would also suggest that you limit the ranges

So instead of:
=IFERROR(INDEX(Transport!G:G,MATCH(Deliveries!A1,Transport!E:E,0)), 20)

Use:
=IFERROR(INDEX(Transport!G1:G1000,MATCH(Deliveries!A1,Transport!E1:E1000,0)), 20)

That is 1000th the size and so will calculate a whole lot faster

Hi Hui,

Thanks for your reply.

At the moment the formula is working well, but I can totally see your point with regard to putting a limit on the ranges.

I'll give that a go.

Many thanks,

Brian

 

[Khalid NGO]

Hi Khalid,

Sorry if I was unclear.

The formula in column D works fine with the sheets it's pulling information from.

I was hoping to add a formula, even if it's using conditional formatting.

If the cell in column D is over typed with Palletways, I want the result from column N (same row) to appear.

If the cell in column D is over typed with Beacon, I want the result from column O (same row) to appear.

If the cell in column D is over typed with Lowes, I want the result from column P (same row) to appear.

Hope this helps

Regards

Brian

Hi Brian,

What are the expected results in your D9 formula, except "20" which is coming when there is an error?
I think you are having only three results, Palletways, Beacon, & Lowes; and you want 54, 33, 45 respectively.

Is this correct?
If yes, see the Red Cells in attached.

If no, I am still in the dark.

Regards,

 

[brianaclark1967]

Hi Khalid,

I've attached a document showing my query.

Your formula on it's own works fine, I just wanted to try and incorporate your new formula to my old one.

Regards

Brian

 

[Khalid NGO]

Hi Brian,

No, actually I had NO CLUE about the RESULTS in D9, I had used =IFERROR(LOOKUP(D9,N7: P7,N9: P9),"") in E9, based on the result in D9.

So, if the outcome is Palletways, Beacon, Lawes ; lookup result would be 54, 33, 45 respectively. You can ignore this if it is creating confusion.

Regards,

Khalid