"IF" "Index-Match"

[mrbox]

I have a conditional index match formula meant to return the price for items if the price is missing. Column A represents the actual number of items, B represents the price of that item and C represents the time at which the time was that price. Column F-G represent the standard prices for the items and at what time the items should be at that price. Column J is the result column; where I want the missing prices for items returned. So if column B = "missing", return the value in column G, if the item name in column A matches column F and if the associated time in, column C, matches column H, otherwise return the price already listed for the item.

This is the formula in column J.
=IF(B2="missing",INDEX(G$1:G$20000,MATCH(1,INDEX((F$1:F$20000=A2)*(H$1:H$20000=C2),),FALSE)),B2)

All of the appropriate values/criteria are where they should be and there doesn't seem to be a sorting issue but I, instead, keep receiving the #N/A error in response.

Fruitdummy.xlsx

(This isn't all of the data it's a dummy representation, of what I am working with.)

 

[texasalynn]

a couple of things - your data in column C is text not actual times, so you need to convert those to true times. Then I used this formula which is an array so needs to be entered with Ctrl + Shift + Enter

=IF(B23="missing",INDEX(G$1:G$20000,MATCH(A23&C23,F$1:F$20000&H$1:H$20000,0)),B23)

 

[mrbox]

a couple of things - your data in column C is text not actual times, so you need to convert those to true times. Then I used this formula which is an array so needs to be entered with Ctrl + Shift + Enter

=IF(B23="missing",INDEX(G$1:G$20000,MATCH(A23&C23,F$1:F$20000&H$1:H$20000,0)),B23)

How would I convert it to actual times? It's not through the cell formatting is it?

 

[texasalynn]

you can do it by multipy by 1. Type 1 in an empty cell and copy that, then select the column with the text formatted time and paste special - multiply. Then you can format the data as time