Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Vlookup information from blank cell
Can anyone help please.
I wish to return the value from D4(saucer)in cells B2 and B7. My worksheet contains a few thousand rows and "saucer" will always correspond to a blank cell. I have been using the formula below,but this does not recognise a blank cell. VLOOKUP(A2,$C$1:$D$8,2,FALSE A B C D 1 prod3 oven prod1 cup 2 saucer prod2 kettle 3 prod1 cup prod1 cup 4 prod2 kettle saucer 5 prod3 oven prod3 oven 6 prod1 cup prod2 kettle 7 saucer prod2 kettle 8 prod3 oven prod1 cup |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Vlookup information from blank cell
One way around it is to put ="" in those empty cells in c1:c8. These cells will
still look empty. Then use a formula like: =VLOOKUP(A2&"",$c$1:$d$8,2,FALSE) Another way: =INDEX($d$1:$d$8,MATCH(A2&"",$c$1:$c$8&"",0)) This is an array formula. Hit ctrl-shift-enter instead of enter. If you do it correctly, excel will wrap curly brackets {} around your formula. (don't type them yourself.) And you can't use the whole column unless you're using xl2007. Doug wrote: Can anyone help please. I wish to return the value from D4(saucer)in cells B2 and B7. My worksheet contains a few thousand rows and "saucer" will always correspond to a blank cell. I have been using the formula below,but this does not recognise a blank cell. VLOOKUP(A2,$C$1:$D$8,2,FALSE A B C D 1 prod3 oven prod1 cup 2 saucer prod2 kettle 3 prod1 cup prod1 cup 4 prod2 kettle saucer 5 prod3 oven prod3 oven 6 prod1 cup prod2 kettle 7 saucer prod2 kettle 8 prod3 oven prod1 cup -- Dave Peterson |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Vlookup information from blank cell
Place in B1, then array-enter the formula ie press CTRL+SHIFT+ENTER
instead of just pressing ENTER: =INDEX($D$1:$D$7,MATCH(TRUE,$C$1:$C$7=A1,0)) Copy B1 down to B7. Adapt the ranges to suit. -- Max Singapore http://savefile.com/projects/236895 xdemechanik --- "Doug" wrote: Can anyone help please. I wish to return the value from D4(saucer)in cells B2 and B7. My worksheet contains a few thousand rows and "saucer" will always correspond to a blank cell. I have been using the formula below,but this does not recognise a blank cell. VLOOKUP(A2,$C$1:$D$8,2,FALSE A B C D 1 prod3 oven prod1 cup 2 saucer prod2 kettle 3 prod1 cup prod1 cup 4 prod2 kettle saucer 5 prod3 oven prod3 oven 6 prod1 cup prod2 kettle 7 saucer prod2 kettle 8 prod3 oven prod1 cup |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Vlookup information from blank cell
Thanks Dave,i'll try it.
"Dave Peterson" wrote: One way around it is to put ="" in those empty cells in c1:c8. These cells will still look empty. Then use a formula like: =VLOOKUP(A2&"",$c$1:$d$8,2,FALSE) Another way: =INDEX($d$1:$d$8,MATCH(A2&"",$c$1:$c$8&"",0)) This is an array formula. Hit ctrl-shift-enter instead of enter. If you do it correctly, excel will wrap curly brackets {} around your formula. (don't type them yourself.) And you can't use the whole column unless you're using xl2007. Doug wrote: Can anyone help please. I wish to return the value from D4(saucer)in cells B2 and B7. My worksheet contains a few thousand rows and "saucer" will always correspond to a blank cell. I have been using the formula below,but this does not recognise a blank cell. VLOOKUP(A2,$C$1:$D$8,2,FALSE A B C D 1 prod3 oven prod1 cup 2 saucer prod2 kettle 3 prod1 cup prod1 cup 4 prod2 kettle saucer 5 prod3 oven prod3 oven 6 prod1 cup prod2 kettle 7 saucer prod2 kettle 8 prod3 oven prod1 cup -- Dave Peterson |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Vlookup information from blank cell
Thanks Max
"Max" wrote: Place in B1, then array-enter the formula ie press CTRL+SHIFT+ENTER instead of just pressing ENTER: =INDEX($D$1:$D$7,MATCH(TRUE,$C$1:$C$7=A1,0)) Copy B1 down to B7. Adapt the ranges to suit. -- Max Singapore http://savefile.com/projects/236895 xdemechanik --- "Doug" wrote: Can anyone help please. I wish to return the value from D4(saucer)in cells B2 and B7. My worksheet contains a few thousand rows and "saucer" will always correspond to a blank cell. I have been using the formula below,but this does not recognise a blank cell. VLOOKUP(A2,$C$1:$D$8,2,FALSE A B C D 1 prod3 oven prod1 cup 2 saucer prod2 kettle 3 prod1 cup prod1 cup 4 prod2 kettle saucer 5 prod3 oven prod3 oven 6 prod1 cup prod2 kettle 7 saucer prod2 kettle 8 prod3 oven prod1 cup |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Vlookup information from blank cell
You're welcome.
Slight amendment to the range in: =INDEX($D$1:$D$7,MATCH(TRUE,$C$1:$C$7=A1,0)) Above should have read as, array-entered in B1, then copied down: =INDEX($D$1:$D$8,MATCH(TRUE,$C$1:$C$8=A1,0)) (I missed row 8 of your range as posted earlier) -- Max Singapore http://savefile.com/projects/236895 xdemechanik --- "Doug" wrote: Thanks Max |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Blank cell is not blank | Excel Discussion (Misc queries) | |||
Using COUNTIF to search for existence | Excel Worksheet Functions | |||
insert date | Excel Worksheet Functions | |||
Urgent date/scheduling calc needed | Excel Worksheet Functions | |||
Possible Lookup Table | Excel Worksheet Functions |