INDEX and MATCH across two worksheets

I am attaching the workbook I am having trouble with.RFQ TEMPLATE.xlsx

When an Item number is input into Cell A3, then a VLOOKUP finds the correct Part # and puts it in G3.

Currently in cell H3, the correct price is displayed based on the Qty in C3. However, currently this only works if the part # in G3 is found in the worksheet "PRICE". I need cell H3 to lookup the correct price in worksheet "CSP" if the part # in G3 is not found in worksheet "PRICE".

Not sure how to do this. I have searched and searched and cannot solve it. Any help is greatly appreciated!

thanks!

=IFERROR(INDEX(PRICE!$C$2:$J$300,MATCH($G3,PRICE!$B$2:$B$300,0),MATCH($C3,PRICE!$C$1:$J$1,0)),IFERROR(INDEX(CSP!$C$2:$H$56,MATCH($G3,CSP!$B$2:$B$56,0),MATCH($C$3,CSP!$C$1:$H$1,0)),""))

Array FOrmula (When applied, you need to press CTRL-SHIFT-ENTER button together) and copied down

Azumi, it's not working. with the formula you gave me it will still return a price if the part # is found in the PRICE worksheet but if I use one from the CP worksheet then cell H3 is blank...any ideas?

Originally Posted by gak67

Try an IFERROR statement, something like this:

Formula:

Please Login or Register  to view this content.

That's it!! Thanks gak67!! saved me a lot of Googling time!!

I spoke too soon....sorry. Both of your answers seem to return correctly if the part # in G3 is located in the "CSP" worksheet, but if the Part # in G3 is found in the "PRICE" worksheet there is an error or blank cell. Any ideas why that would be? I have tried both of your suggestions multiple times. Thank you again for your help!

Which Item that found in PRICE sheet and give you an error?

Try the very first one....item # 643169097933 in cell A2

Yes that is strange. I just downloaded the file again from this website and tried it in the first row (Row 3) and it worked...but then when I replaced that value in A3 with a number from the "CSP" worksheet in A8 (00885074388571)that didn't work. Does that work for you?

Maybe that part not avalaible in both 2 sheets? Is that possible?

It gave me error results

A part is available in only one sheet or the other. A part will never exist in both sheets. That is the reason I need it to search through both sheets. Sorry if that is confusing.

Wait a sec

ok...no problem

Ah... you have 2 different Column Header in PRICE sheet and CSP sheet

PRICE = 20, 30, 40

SCP = 1, 2, 3, 4

But you condition is 20, 50 and so on

So will not find in SCP sheet

I am not sure I understand.....could you help me understand better what you are saying please.
The CSP sheet is for specialty parts and that is why the QTY headers are different because the customer only orders smaller quantities of the parts in that list.

I think I understand. ANd I just realized a problem. If I type in the exact quantity for example 20, 30, 40 for an item in the PRICE sheet it will give the correct price, but if I type in a quantity of 32 or 41 or 57 it does not. It should give a price when I type in those odd values. Those are price breaks. For example if an item has a price of $135 for 20 parts and a price of $128 for 30 parts, the price for anything from 20 to 29 parts should be $135. at a quantity of 30 the price would change to $128. Does that make sense? Those are price break points. Sorry for the confusion.

Azumi, did you see my last post? Any ideas?

Awesome, Azumi! It works great! Thanks again for your help!