| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Using INDEX() to Locate Variable Data
Here is my formula for cell I26: *=IF(G26=" Armor Slot 2","",INDEX(G1:P97,MATCH(G25,N3:O97,0),16))* Here is an image of the sheet I'm working on: http://img301.imageshack.us/img301/1...lpsheetme1.jpg When I activate the dropdown menu for the cell that says Armor Slot 2 and choose another "armor", I get a #N/A error in cell I26. Since using a string of IF statements to check which armor I'm searching for would result in the formula being too long, can anyone please help me find out what I'm doing wrong in this formula that's resulting in an error? I'm trying to choose an armor from a list of cells in column N and then display the value that corresponds to it, from column P, into cell I26. (The list of armors extends down to row 97.) Help would be MUCH appreciated! Shadowboy -- Shadowboy ------------------------------------------------------------------------ Shadowboy's Profile: http://www.excelforum.com/member.php...o&userid=37804 View this thread: http://www.excelforum.com/showthread...hreadid=573668 |
|
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Using INDEX() to Locate Variable Data
Use VLOOKUP in I26 to retrieve value from column P
=VLOOKUP(G25,N3:O97,2,FALSE) Looks up selection from G25 and finds corresponding entry in N:O To allow for errors: =If(ISERROR(VLOOKUP(G25,N3:O97,2,FALSE)),"",VLOOKU P(G25,N3:O97,2,FALSE)) in J26 to retrieve value from column O =If(ISERROR(VLOOKUP(G25,N3:O97,3,FALSE)),"",VLOOKU P(G25,N3:O97,3,FALSE)) HTH "Shadowboy" wrote: Here is my formula for cell I26: *=IF(G26=" Armor Slot 2","",INDEX(G1:P97,MATCH(G25,N3:O97,0),16))* Here is an image of the sheet I'm working on: http://img301.imageshack.us/img301/1...lpsheetme1.jpg When I activate the dropdown menu for the cell that says Armor Slot 2 and choose another "armor", I get a #N/A error in cell I26. Since using a string of IF statements to check which armor I'm searching for would result in the formula being too long, can anyone please help me find out what I'm doing wrong in this formula that's resulting in an error? I'm trying to choose an armor from a list of cells in column N and then display the value that corresponds to it, from column P, into cell I26. (The list of armors extends down to row 97.) Help would be MUCH appreciated! Shadowboy -- Shadowboy ------------------------------------------------------------------------ Shadowboy's Profile: http://www.excelforum.com/member.php...o&userid=37804 View this thread: http://www.excelforum.com/showthread...hreadid=573668 |
|
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Using INDEX() to Locate Variable Data
Typo alert.
Without reading the OP's message... If the formula is retrieving from column P, shouldn't it be included in the lookup range--and if that look up range starts in column O, then it would be the 3rd column: =VLOOKUP(G25,N3:O97,2,FALSE) becomes =VLOOKUP(G25,N3:p97,3,FALSE) (in all the formulas) Toppers wrote: Use VLOOKUP in I26 to retrieve value from column P =VLOOKUP(G25,N3:O97,2,FALSE) Looks up selection from G25 and finds corresponding entry in N:O To allow for errors: =If(ISERROR(VLOOKUP(G25,N3:O97,2,FALSE)),"",VLOOKU P(G25,N3:O97,2,FALSE)) in J26 to retrieve value from column O =If(ISERROR(VLOOKUP(G25,N3:O97,3,FALSE)),"",VLOOKU P(G25,N3:O97,3,FALSE)) HTH "Shadowboy" wrote: Here is my formula for cell I26: *=IF(G26=" Armor Slot 2","",INDEX(G1:P97,MATCH(G25,N3:O97,0),16))* Here is an image of the sheet I'm working on: http://img301.imageshack.us/img301/1...lpsheetme1.jpg When I activate the dropdown menu for the cell that says Armor Slot 2 and choose another "armor", I get a #N/A error in cell I26. Since using a string of IF statements to check which armor I'm searching for would result in the formula being too long, can anyone please help me find out what I'm doing wrong in this formula that's resulting in an error? I'm trying to choose an armor from a list of cells in column N and then display the value that corresponds to it, from column P, into cell I26. (The list of armors extends down to row 97.) Help would be MUCH appreciated! Shadowboy -- Shadowboy ------------------------------------------------------------------------ Shadowboy's Profile: http://www.excelforum.com/member.php...o&userid=37804 View this thread: http://www.excelforum.com/showthread...hreadid=573668 -- Dave Peterson |
|
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Using INDEX() to Locate Variable Data
..... bad day at the office (home actually)! Thanks ...again!
"Dave Peterson" wrote: Typo alert. Without reading the OP's message... If the formula is retrieving from column P, shouldn't it be included in the lookup range--and if that look up range starts in column O, then it would be the 3rd column: =VLOOKUP(G25,N3:O97,2,FALSE) becomes =VLOOKUP(G25,N3:p97,3,FALSE) (in all the formulas) Toppers wrote: Use VLOOKUP in I26 to retrieve value from column P =VLOOKUP(G25,N3:O97,2,FALSE) Looks up selection from G25 and finds corresponding entry in N:O To allow for errors: =If(ISERROR(VLOOKUP(G25,N3:O97,2,FALSE)),"",VLOOKU P(G25,N3:O97,2,FALSE)) in J26 to retrieve value from column O =If(ISERROR(VLOOKUP(G25,N3:O97,3,FALSE)),"",VLOOKU P(G25,N3:O97,3,FALSE)) HTH "Shadowboy" wrote: Here is my formula for cell I26: *=IF(G26=" Armor Slot 2","",INDEX(G1:P97,MATCH(G25,N3:O97,0),16))* Here is an image of the sheet I'm working on: http://img301.imageshack.us/img301/1...lpsheetme1.jpg When I activate the dropdown menu for the cell that says Armor Slot 2 and choose another "armor", I get a #N/A error in cell I26. Since using a string of IF statements to check which armor I'm searching for would result in the formula being too long, can anyone please help me find out what I'm doing wrong in this formula that's resulting in an error? I'm trying to choose an armor from a list of cells in column N and then display the value that corresponds to it, from column P, into cell I26. (The list of armors extends down to row 97.) Help would be MUCH appreciated! Shadowboy -- Shadowboy ------------------------------------------------------------------------ Shadowboy's Profile: http://www.excelforum.com/member.php...o&userid=37804 View this thread: http://www.excelforum.com/showthread...hreadid=573668 -- Dave Peterson |
|
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Using INDEX() to Locate Variable Data
Thank you both for your fanstastic responses. My problem is solved and I'm continuing along my way. -- Shadowboy ------------------------------------------------------------------------ Shadowboy's Profile: http://www.excelforum.com/member.php...o&userid=37804 View this thread: http://www.excelforum.com/showthread...hreadid=573668 |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Dynamic scenarios - more than 2 variable using data tables in Exc | Excel Discussion (Misc queries) | |||
| Vlookup to Return a Range of Data | Excel Discussion (Misc queries) | |||
| Variable data range help | Charts and Charting in Excel | |||
| Can the column index in a cell address be made variable? | Excel Discussion (Misc queries) | |||
| Pulling data from 1 sheet to another | Excel Worksheet Functions | |||
Linear Mode
