LOOKUP problem

[Lisa Cowan]

Dear All.

I currently use the below formula in Process Details of a 5 page quote
spreadsheet - the F column refers back to the Master parts list, column B and
the price in column H is returned and goes into column G on Process details.
the problem is we are still adding new items to the parts list and it is now
causing a problem when returning a correct price as it does not seem to be
tracking properly and returning a price from one or two lines up. To keep the
parts list in alphabetical order I am inserting lines to put the new items
in. It is only happening with the new items but not all of the new items.
Feels more like a ghost in the machine but I am sure that you guys have a
much more sensible explaination for it.

=IF(ISNA(LOOKUP(F564,Master!$B$5:$B$161,Master!$H$5:$H$161)),"",LOOKUP(F564,Master!$B$5:$B$161,Master!$H$5:$H$161))

thanks for any help you can give.
Lisa

 

[Eduardo]

Hi, try

=IF(ISNA(INDEX(Master!$H$5:$H$161,MATCH(F564,Master!$B$5:$B$161))),"",INDEX(Master!$H$5:$H$161,MATCH(F564,Master!$B$5:$B$161)))


you don't need to have the list in alphabetical order

 

[Lisa Cowan]

Thanks for that. Do you happen to know why this is happening with my existing
formula?

As far as the alphabetical order is concerned it is more for ease of search
when you are looking for a part rather than in relation to the formula.

thanks

 

[Eduardo]

Hi Lisa,
for the lookup formula first you have the value to lookup then you enter the
range, the the column number where you want to pull your information and then
false if you want exact match in your case your formula should be

=IF(ISNA(LOOKUP(F564,Master!$B$5:$H$161,7,false)),"",LOOKUP(F564,Master!$B$5:$H$161,7,false))

 

[Lisa Cowan]

Hi

Sorry to be a pain but your formula is doing the same thing, I have two sink
parts which I have added to the list. when I add the first one the correct
price appears when i select it in the Process details sheet, however when I
add the second item below the first one on the Parts list the price on the
Process Details for the previous item changes to the price of the second item
which I have just added to the Parts list i.e it takes the price of the item
above.
Like I said if you know what is causing this it may not require a change in
formula.

thanks

 

[Lisa Cowan]

Hi

My original formula worked fine and did provide exact results it only
stopped working yesterday with the last batch of additions to the Parts List,
I am not to sure where the FALSE comes in as no item is added without a
corresponding price. The columns between B and H are not required in the
other sheets which is why I have only entered ranges of B and H rather than
everything inbetween as well.

Maybe this clarifies things a bit more, but if you could explain your
reasoning that would also help me see where you are coming from.

 

[Eduardo]

Hi Lisa,
could you forward a sample of you spreadsheet I will take a look, please in
the email mention this discussion group and the problems you are having
forward it to (e-mail address removed)

 

[Lisa Cowan]

Hi

I think I have fixed it - some how there was a drop down menu in the H
column of the Parts List so when I was inserting new items it was not able to
return the exact price. Well now I have removed this things seem to have
stabalised.

thanks for all your help

 

[Eduardo]

your welcome thank you for letting me know

Hi

I think I have fixed it - some how there was a drop down menu in the H
column of the Parts List so when I was inserting new items it was not able to
return the exact price. Well now I have removed this things seem to have
stabalised.

thanks for all your help