Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Excel formula
Hi! I hope someone out here can help me.
I'm trying to make my spreadsheet for work clever! What i want is, if a cell value, in a specified range, equals any of many specified values from a different range, then for a seperate cell to reflect what is in the original cell. I don't know f this is possible but any help will be appreciated. Thanks |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Excel formula
Not real clear what you want. Here's my best guess.
Testing if *any* number in the range A1:A10 also appears in the range J1:J20: =INDEX(A1:A10,MATCH(TRUE,INDEX(ISNUMBER(MATCH(A1:A 10,J1:J20,0)),,1),0)) A result of #N/A means no numbers in A1:A10 appear in J1:J20. If more than one number from A1:A10 appears in J1:J20 the formula returns the *first* number that meets the condition. -- Biff Microsoft Excel MVP "Stu" wrote in message ... Hi! I hope someone out here can help me. I'm trying to make my spreadsheet for work clever! What i want is, if a cell value, in a specified range, equals any of many specified values from a different range, then for a seperate cell to reflect what is in the original cell. I don't know f this is possible but any help will be appreciated. Thanks |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Excel formula
Something like...
=IF(ISERROR(MATCH(A1, $B$1:$B$10, 0)),"-",A1) .... will give you the contents of A1 if they matches a cell in B1:B10. Otherwise, you'll get a dash. - David Stu wrote: Hi! I hope someone out here can help me. I'm trying to make my spreadsheet for work clever! What i want is, if a cell value, in a specified range, equals any of many specified values from a different range, then for a seperate cell to reflect what is in the original cell. I don't know f this is possible but any help will be appreciated. Thanks |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Excel formula
Thanks T.Valko and David Hilberg what im trying to achieve and what you have
suggested sounds alined. I can go into a bit more detail if that will help yourselves and anyone else. In the range A2:A90 (for e.g), there will be many product numbers for each product. Each product costs a different amount. What i want is for the spreadsheet to (by me typing in the product number in A2:A90), look in a table on the same sheet or different sheet, does not really matter, for the corresponding price and enter that price in C2. So by me typing in the product number in A2 i want the price to come up in C2 auto from a table with all product numbers and prices in. Hope this is more help Thanks "David Hilberg" wrote: Something like... =IF(ISERROR(MATCH(A1, $B$1:$B$10, 0)),"-",A1) .... will give you the contents of A1 if they matches a cell in B1:B10. Otherwise, you'll get a dash. - David Stu wrote: Hi! I hope someone out here can help me. I'm trying to make my spreadsheet for work clever! What i want is, if a cell value, in a specified range, equals any of many specified values from a different range, then for a seperate cell to reflect what is in the original cell. I don't know f this is possible but any help will be appreciated. Thanks |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Excel 2002 Formula: Urgent Conditional Formula Required Right Away - if possible | Excel Discussion (Misc queries) | |||
Build excel formula using field values as text in the formula | Excel Worksheet Functions | |||
Excel Formula Doesn't Execute (Shows formula-not the calcuation) | Excel Discussion (Misc queries) | |||
Excel 2002 formula displayed not value formula option not checked | Excel Worksheet Functions | |||
How do I view formula results intead of formula in excel? | Excel Worksheet Functions |