SOLVED

VLOOKUP #Value! error (includes using INDEX function)

%3CLINGO-SUB%20id%3D%22lingo-sub-3134076%22%20slang%3D%22en-US%22%3EVLOOKUP%20%23Value!%20error%20(includes%20using%20INDEX%20function)%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-3134076%22%20slang%3D%22en-US%22%3E%3CP%3EHi%2C%3C%2FP%3E%3CP%3EI'm%20trying%20to%20create%20a%20cell%20calculation%20worksheet%20that%20will%20calculate%20the%20number%20and%20size%20of%20flasks%20that%20I%20need%20to%20seed%20a%20given%20number%20of%20plates.%20For%20example%2C%20if%20I%20wanted%20to%20seed%2010%20plates%2C%20the%20worksheet%20would%20calculate%20how%20the%20size%20and%20amount%20of%20flasks%20I%20need%20to%20do%20that.%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThe%20formula%20that%20I%20am%20using%20is%3A%3C%2FP%3E%3CP%3E%26nbsp%3B%3CSTRONG%3E%3DROUNDUP((C7%2F((MIN(INDEX(%24C%2425%3A%24D%2429%2C2%2CFALSE)))))%2C%200)%3C%2FSTRONG%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EIt%20returns%20the%20number%20of%20flasks%20I%20need%20to%20use%20to%20seed%20the%20plates.%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CTABLE%20width%3D%22229%22%3E%3CTBODY%3E%3CTR%3E%3CTD%20width%3D%22125%22%3EPlate%20Needed%3C%2FTD%3E%3CTD%20width%3D%22104%22%3E20%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3E%23%20of%20Flasks%20Needed%3C%2FTD%3E%3CTD%3E2%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3EFlask%20Size%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3C%2FTR%3E%3C%2FTBODY%3E%3C%2FTABLE%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EHowever%2C%20I%20can't%20figure%20out%20how%20to%20get%20it%20so%20it%20will%20tell%20me%20the%20size%20of%20flask%20I%20need%20it%20to%20use.%26nbsp%3B%3C%2FP%3E%3CP%3EThe%20table%20that%20the%20INDEX%20function%20is%20referencing%20is%3A%20(This%20table%20tells%20me%20how%20many%20plates%20can%20be%20seeded%20by%20each%20flask%20size.%20E.g.%2C%20One%20T225%20flask%20can%20seed%2011%20plates).%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CTABLE%20width%3D%22208%22%3E%3CTBODY%3E%3CTR%3E%3CTD%20width%3D%22104%22%3EFlask%20Size%3C%2FTD%3E%3CTD%20width%3D%22104%22%3EPlates%20per%20Flask%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3ET225%3C%2FTD%3E%3CTD%3E11%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3ET175%3C%2FTD%3E%3CTD%3E10%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3ET150%3C%2FTD%3E%3CTD%3E6%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3ET75%3C%2FTD%3E%3CTD%3E6%3C%2FTD%3E%3C%2FTR%3E%3C%2FTBODY%3E%3C%2FTABLE%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI'm%20having%20trouble%2C%20getting%20the%20flask%20size%20to%20correspond%20with%20the%20value%20given%20for%20the%20number%20of%20flasks%20need.%20So%20in%20this%20example%2C%202XT225%20flasks%20would%20seed%2020%20plates%20(because%20the%20T225%20flask%20size%20was%20the%20value%20given%20based%20on%20the%20calculation%20above)%20NOTE%3A%20It%20could%20also%20work%20for%20the%20T175%20flask%2C%20but%20the%20issue%20is%20the%20same.%26nbsp%3B%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-3134076%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-3135921%22%20slang%3D%22en-US%22%3ERe%3A%20VLOOKUP%20%23Value!%20error%20(includes%20using%20INDEX%20function)%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-3135921%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F1300165%22%20target%3D%22_blank%22%3E%40tanoya%3C%2FA%3E%26nbsp%3BThat%20calls%20for%20an%20XLOOKUP%20function%2C%20see%20attached.%3C%2FP%3E%3C%2FLINGO-BODY%3E
New Contributor

Hi,

I'm trying to create a cell calculation worksheet that will calculate the number and size of flasks that I need to seed a given number of plates. For example, if I wanted to seed 10 plates, the worksheet would calculate how the size and amount of flasks I need to do that. 

 

The formula that I am using is:

 =ROUNDUP((C7/((MIN(INDEX($C$25:$D$29,2,FALSE))))), 0)

 

It returns the number of flasks I need to use to seed the plates. 

 

Plate Needed20
# of Flasks Needed2
Flask Size 

 

However, I can't figure out how to get it so it will tell me the size of flask I need it to use. 

The table that the INDEX function is referencing is: (This table tells me how many plates can be seeded by each flask size. E.g., One T225 flask can seed 11 plates).

 

Flask SizePlates per Flask
T22511
T17510
T1506
T756

 

I'm having trouble, getting the flask size to correspond with the value given for the number of flasks need. So in this example, 2XT225 flasks would seed 20 plates (because the T225 flask size was the value given based on the calculation above) NOTE: It could also work for the T175 flask, but the issue is the same.  

2 Replies
best response confirmed by tanoya (New Contributor)
Solution

@tanoya That calls for an XLOOKUP function, see attached.

Thanks! That fixed it!