|
vlookup again
Hi experts
Please solve this plane sample with formula A B C .............................. H I 1 . . . ..............................10 1 2 10 . 1 ............................. 10 2 3 . . . ..............................10 3 4 . 10 2 .............................N/A N/A 5 . . . 6 . 10 3 7 . . . insted of N/A in last vlookup replace blank Thanks in advance |
vlookup again
Bijan
I Think you will need to warp it in an IF(ISNA... like =IF(ISNA(Your_Original_Vlookup),"",Your_Original_V lookup) -- HTH Nick Hodge Microsoft MVP - Excel Southampton, England DTHIS www.nickhodge.co.uk "bijan" wrote in message ... Hi experts Please solve this plane sample with formula A B C .............................. H I 1 . . . ..............................10 1 2 10 . 1 ............................. 10 2 3 . . . ..............................10 3 4 . 10 2 .............................N/A N/A 5 . . . 6 . 10 3 7 . . . insted of N/A in last vlookup replace blank Thanks in advance |
vlookup again
or even wrap ;-)
-- HTH Nick Hodge Microsoft MVP - Excel Southampton, England DTHIS www.nickhodge.co.uk "Nick Hodge" wrote in message ... Bijan I Think you will need to warp it in an IF(ISNA... like =IF(ISNA(Your_Original_Vlookup),"",Your_Original_V lookup) -- HTH Nick Hodge Microsoft MVP - Excel Southampton, England DTHIS www.nickhodge.co.uk "bijan" wrote in message ... Hi experts Please solve this plane sample with formula A B C .............................. H I 1 . . . ..............................10 1 2 10 . 1 ............................. 10 2 3 . . . ..............................10 3 4 . 10 2 .............................N/A N/A 5 . . . 6 . 10 3 7 . . . insted of N/A in last vlookup replace blank Thanks in advance |
vlookup again
Yes, you are right,but how can i do it , should i write 4 formula from h1 to
h4? "Nick Hodge" wrote: or even wrap ;-) -- HTH Nick Hodge Microsoft MVP - Excel Southampton, England DTHIS www.nickhodge.co.uk "Nick Hodge" wrote in message ... Bijan I Think you will need to warp it in an IF(ISNA... like =IF(ISNA(Your_Original_Vlookup),"",Your_Original_V lookup) -- HTH Nick Hodge Microsoft MVP - Excel Southampton, England DTHIS www.nickhodge.co.uk "bijan" wrote in message ... Hi experts Please solve this plane sample with formula A B C .............................. H I 1 . . . ..............................10 1 2 10 . 1 ............................. 10 2 3 . . . ..............................10 3 4 . 10 2 .............................N/A N/A 5 . . . 6 . 10 3 7 . . . insted of N/A in last vlookup replace blank Thanks in advance |
vlookup again
Bijan
Post your current formula -- HTH Nick Hodge Microsoft MVP - Excel Southampton, England DTHIS www.nickhodge.co.uk "bijan" wrote in message ... Yes, you are right,but how can i do it , should i write 4 formula from h1 to h4? "Nick Hodge" wrote: or even wrap ;-) -- HTH Nick Hodge Microsoft MVP - Excel Southampton, England DTHIS www.nickhodge.co.uk "Nick Hodge" wrote in message ... Bijan I Think you will need to warp it in an IF(ISNA... like =IF(ISNA(Your_Original_Vlookup),"",Your_Original_V lookup) -- HTH Nick Hodge Microsoft MVP - Excel Southampton, England DTHIS www.nickhodge.co.uk "bijan" wrote in message ... Hi experts Please solve this plane sample with formula A B C .............................. H I 1 . . . ..............................10 1 2 10 . 1 ............................. 10 2 3 . . . ..............................10 3 4 . 10 2 .............................N/A N/A 5 . . . 6 . 10 3 7 . . . insted of N/A in last vlookup replace blank Thanks in advance |
vlookup again
in"H1" =vlookup(a1,tbl,3,false)
But it just return first value from my table, i need obtain secound record in H2 and so on "Nick Hodge" wrote: Bijan Post your current formula -- HTH Nick Hodge Microsoft MVP - Excel Southampton, England DTHIS www.nickhodge.co.uk "bijan" wrote in message ... Yes, you are right,but how can i do it , should i write 4 formula from h1 to h4? "Nick Hodge" wrote: or even wrap ;-) -- HTH Nick Hodge Microsoft MVP - Excel Southampton, England DTHIS www.nickhodge.co.uk "Nick Hodge" wrote in message ... Bijan I Think you will need to warp it in an IF(ISNA... like =IF(ISNA(Your_Original_Vlookup),"",Your_Original_V lookup) -- HTH Nick Hodge Microsoft MVP - Excel Southampton, England DTHIS www.nickhodge.co.uk "bijan" wrote in message ... Hi experts Please solve this plane sample with formula A B C .............................. H I 1 . . . ..............................10 1 2 10 . 1 ............................. 10 2 3 . . . ..............................10 3 4 . 10 2 .............................N/A N/A 5 . . . 6 . 10 3 7 . . . insted of N/A in last vlookup replace blank Thanks in advance |
vlookup again
Bijan
I'm sorry if I'm not understanding, but presuming tbl is a range name, then just copying the formula down (Dragging using the fill handle if you like) should change the references in the formula as the lookup value (A1 in your example) is relative and when dragged down to the next cell down that will automatically become A2 so you will be looking up the value in A2 in your range (tbl) and returning the match 3 columns to the right in tbl. To return a blank when you get a #N/A then use the formula structure I posted so you end up with, in H1 =IF(ISNA(VLOOKUP(A1,tbl,3,FALSE)),"",VLOOKUP(A1,tb l,3,FALSE)) Now just copy this to the cells below -- HTH Nick Hodge Microsoft MVP - Excel Southampton, England DTHIS www.nickhodge.co.uk "bijan" wrote in message ... in"H1" =vlookup(a1,tbl,3,false) But it just return first value from my table, i need obtain secound record in H2 and so on "Nick Hodge" wrote: Bijan Post your current formula -- HTH Nick Hodge Microsoft MVP - Excel Southampton, England DTHIS www.nickhodge.co.uk "bijan" wrote in message ... Yes, you are right,but how can i do it , should i write 4 formula from h1 to h4? "Nick Hodge" wrote: or even wrap ;-) -- HTH Nick Hodge Microsoft MVP - Excel Southampton, England DTHIS www.nickhodge.co.uk "Nick Hodge" wrote in message ... Bijan I Think you will need to warp it in an IF(ISNA... like =IF(ISNA(Your_Original_Vlookup),"",Your_Original_V lookup) -- HTH Nick Hodge Microsoft MVP - Excel Southampton, England DTHIS www.nickhodge.co.uk "bijan" wrote in message ... Hi experts Please solve this plane sample with formula A B C .............................. H I 1 . . . ..............................10 1 2 10 . 1 ............................. 10 2 3 . . . ..............................10 3 4 . 10 2 .............................N/A N/A 5 . . . 6 . 10 3 7 . . . insted of N/A in last vlookup replace blank Thanks in advance |
vlookup again
nick
I posted an illastration "Nick Hodge" wrote: Bijan I'm sorry if I'm not understanding, but presuming tbl is a range name, then just copying the formula down (Dragging using the fill handle if you like) should change the references in the formula as the lookup value (A1 in your example) is relative and when dragged down to the next cell down that will automatically become A2 so you will be looking up the value in A2 in your range (tbl) and returning the match 3 columns to the right in tbl. To return a blank when you get a #N/A then use the formula structure I posted so you end up with, in H1 =IF(ISNA(VLOOKUP(A1,tbl,3,FALSE)),"",VLOOKUP(A1,tb l,3,FALSE)) Now just copy this to the cells below -- HTH Nick Hodge Microsoft MVP - Excel Southampton, England DTHIS www.nickhodge.co.uk "bijan" wrote in message ... in"H1" =vlookup(a1,tbl,3,false) But it just return first value from my table, i need obtain secound record in H2 and so on "Nick Hodge" wrote: Bijan Post your current formula -- HTH Nick Hodge Microsoft MVP - Excel Southampton, England DTHIS www.nickhodge.co.uk "bijan" wrote in message ... Yes, you are right,but how can i do it , should i write 4 formula from h1 to h4? "Nick Hodge" wrote: or even wrap ;-) -- HTH Nick Hodge Microsoft MVP - Excel Southampton, England DTHIS www.nickhodge.co.uk "Nick Hodge" wrote in message ... Bijan I Think you will need to warp it in an IF(ISNA... like =IF(ISNA(Your_Original_Vlookup),"",Your_Original_V lookup) -- HTH Nick Hodge Microsoft MVP - Excel Southampton, England DTHIS www.nickhodge.co.uk "bijan" wrote in message ... Hi experts Please solve this plane sample with formula A B C .............................. H I 1 . . . ..............................10 1 2 10 . 1 ............................. 10 2 3 . . . ..............................10 3 4 . 10 2 .............................N/A N/A 5 . . . 6 . 10 3 7 . . . insted of N/A in last vlookup replace blank Thanks in advance |
vlookup again
Bijan
Sorry, I must be having one of my dumb days. I see Bijan's illustration and his formula example and my response is based on that. Can anyone give another slant on his need, because I can't see it? -- HTH Nick Hodge Microsoft MVP - Excel Southampton, England DTHIS www.nickhodge.co.uk "bijan" wrote in message ... nick I posted an illastration "Nick Hodge" wrote: Bijan I'm sorry if I'm not understanding, but presuming tbl is a range name, then just copying the formula down (Dragging using the fill handle if you like) should change the references in the formula as the lookup value (A1 in your example) is relative and when dragged down to the next cell down that will automatically become A2 so you will be looking up the value in A2 in your range (tbl) and returning the match 3 columns to the right in tbl. To return a blank when you get a #N/A then use the formula structure I posted so you end up with, in H1 =IF(ISNA(VLOOKUP(A1,tbl,3,FALSE)),"",VLOOKUP(A1,tb l,3,FALSE)) Now just copy this to the cells below -- HTH Nick Hodge Microsoft MVP - Excel Southampton, England DTHIS www.nickhodge.co.uk "bijan" wrote in message ... in"H1" =vlookup(a1,tbl,3,false) But it just return first value from my table, i need obtain secound record in H2 and so on "Nick Hodge" wrote: Bijan Post your current formula -- HTH Nick Hodge Microsoft MVP - Excel Southampton, England DTHIS www.nickhodge.co.uk "bijan" wrote in message ... Yes, you are right,but how can i do it , should i write 4 formula from h1 to h4? "Nick Hodge" wrote: or even wrap ;-) -- HTH Nick Hodge Microsoft MVP - Excel Southampton, England DTHIS www.nickhodge.co.uk "Nick Hodge" wrote in message ... Bijan I Think you will need to warp it in an IF(ISNA... like =IF(ISNA(Your_Original_Vlookup),"",Your_Original_V lookup) -- HTH Nick Hodge Microsoft MVP - Excel Southampton, England DTHIS www.nickhodge.co.uk "bijan" wrote in message ... Hi experts Please solve this plane sample with formula A B C .............................. H I 1 . . . ..............................10 1 2 10 . 1 ............................. 10 2 3 . . . ..............................10 3 4 . 10 2 .............................N/A N/A 5 . . . 6 . 10 3 7 . . . insted of N/A in last vlookup replace blank Thanks in advance |
vlookup again
Nick
I posted an illastration ,But it seems something wrong in your email address to deliver my email please write your correct email address "Nick Hodge" wrote: Bijan I'm sorry if I'm not understanding, but presuming tbl is a range name, then just copying the formula down (Dragging using the fill handle if you like) should change the references in the formula as the lookup value (A1 in your example) is relative and when dragged down to the next cell down that will automatically become A2 so you will be looking up the value in A2 in your range (tbl) and returning the match 3 columns to the right in tbl. To return a blank when you get a #N/A then use the formula structure I posted so you end up with, in H1 =IF(ISNA(VLOOKUP(A1,tbl,3,FALSE)),"",VLOOKUP(A1,tb l,3,FALSE)) Now just copy this to the cells below -- HTH Nick Hodge Microsoft MVP - Excel Southampton, England DTHIS www.nickhodge.co.uk "bijan" wrote in message ... in"H1" =vlookup(a1,tbl,3,false) But it just return first value from my table, i need obtain secound record in H2 and so on "Nick Hodge" wrote: Bijan Post your current formula -- HTH Nick Hodge Microsoft MVP - Excel Southampton, England DTHIS www.nickhodge.co.uk "bijan" wrote in message ... Yes, you are right,but how can i do it , should i write 4 formula from h1 to h4? "Nick Hodge" wrote: or even wrap ;-) -- HTH Nick Hodge Microsoft MVP - Excel Southampton, England DTHIS www.nickhodge.co.uk "Nick Hodge" wrote in message ... Bijan I Think you will need to warp it in an IF(ISNA... like =IF(ISNA(Your_Original_Vlookup),"",Your_Original_V lookup) -- HTH Nick Hodge Microsoft MVP - Excel Southampton, England DTHIS www.nickhodge.co.uk "bijan" wrote in message ... Hi experts Please solve this plane sample with formula A B C .............................. H I 1 . . . ..............................10 1 2 10 . 1 ............................. 10 2 3 . . . ..............................10 3 4 . 10 2 .............................N/A N/A 5 . . . 6 . 10 3 7 . . . insted of N/A in last vlookup replace blank Thanks in advance |
vlookup again
Bijan
Take out the TAKETHISOUT and the ANDTHIS (Basically the bits in upper case) -- HTH Nick Hodge Microsoft MVP - Excel Southampton, England DTHIS www.nickhodge.co.uk "bijan" wrote in message ... Nick I posted an illastration ,But it seems something wrong in your email address to deliver my email please write your correct email address "Nick Hodge" wrote: Bijan I'm sorry if I'm not understanding, but presuming tbl is a range name, then just copying the formula down (Dragging using the fill handle if you like) should change the references in the formula as the lookup value (A1 in your example) is relative and when dragged down to the next cell down that will automatically become A2 so you will be looking up the value in A2 in your range (tbl) and returning the match 3 columns to the right in tbl. To return a blank when you get a #N/A then use the formula structure I posted so you end up with, in H1 =IF(ISNA(VLOOKUP(A1,tbl,3,FALSE)),"",VLOOKUP(A1,tb l,3,FALSE)) Now just copy this to the cells below -- HTH Nick Hodge Microsoft MVP - Excel Southampton, England DTHIS www.nickhodge.co.uk "bijan" wrote in message ... in"H1" =vlookup(a1,tbl,3,false) But it just return first value from my table, i need obtain secound record in H2 and so on "Nick Hodge" wrote: Bijan Post your current formula -- HTH Nick Hodge Microsoft MVP - Excel Southampton, England DTHIS www.nickhodge.co.uk "bijan" wrote in message ... Yes, you are right,but how can i do it , should i write 4 formula from h1 to h4? "Nick Hodge" wrote: or even wrap ;-) -- HTH Nick Hodge Microsoft MVP - Excel Southampton, England DTHIS www.nickhodge.co.uk "Nick Hodge" wrote in message ... Bijan I Think you will need to warp it in an IF(ISNA... like =IF(ISNA(Your_Original_Vlookup),"",Your_Original_V lookup) -- HTH Nick Hodge Microsoft MVP - Excel Southampton, England DTHIS www.nickhodge.co.uk "bijan" wrote in message ... Hi experts Please solve this plane sample with formula A B C .............................. H I 1 . . . ..............................10 1 2 10 . 1 ............................. 10 2 3 . . . ..............................10 3 4 . 10 2 .............................N/A N/A 5 . . . 6 . 10 3 7 . . . insted of N/A in last vlookup replace blank Thanks in advance |
vlookup again
H1:
=IF(ISERROR(SMALL(IF(($A$1:$A$6=10)+($B$1:$B$6=10) 0,ROW($A$1:$A$6)),ROW(A1))),"",10) Enter with control + Shift + Enter rather than just enter since this is an array formula I1: =IF(ISERROR(SMALL(IF(($A$1:$A$7=10)+($B$1:$B$7=10) 0,ROW($A$1:$A$7)),ROW(A1))),"",INDEX($C$1:$C$7,SM ALL(IF(($A$1:$A$6=10)+($B$1:$B$7=10)0,ROW($A$1:$A $7)),ROW(A1)),1)) Entered with Ctrl+Shift+Enter rather than just enter since this is an array formula Select H1:I1 and drag fill down the column. -- Regards, Tom Ogilvy "bijan" wrote in message ... Hi experts Please solve this plane sample with formula A B C .............................. H I 1 . . . ..............................10 1 2 10 . 1 ............................. 10 2 3 . . . ..............................10 3 4 . 10 2 .............................N/A N/A 5 . . . 6 . 10 3 7 . . . insted of N/A in last vlookup replace blank Thanks in advance |
vlookup again
My test data only went to row 6, so I didn't get all the formula ranges
changes. Let me try again: H1: =IF(ISERROR(SMALL(IF(($A$1:$A$7=10)+($B$1:$B$7=10) 0,ROW($A$1:$A$7)),ROW(A1))),"",10) Enter with control + Shift + Enter rather than just enter since this is an array formula I1: =IF(ISERROR(SMALL(IF(($A$1:$A$7=10)+($B$1:$B$7=10) 0,ROW($A$1:$A$7)),ROW(A1))),"",INDEX($C$1:$C$7,SM ALL(IF(($A$1:$A$7=10)+($B$1:$B$7=10)0,ROW($A$1:$A $7)),ROW(A1)),1)) Entered with Ctrl+Shift+Enter rather than just enter since this is an array formula Select H1:I1 and drag fill down the column. -- Regards, Tom Ogilvy "Tom Ogilvy" wrote in message ... H1: =IF(ISERROR(SMALL(IF(($A$1:$A$6=10)+($B$1:$B$6=10) 0,ROW($A$1:$A$6)),ROW(A1))),"",10) Enter with control + Shift + Enter rather than just enter since this is an array formula I1: =IF(ISERROR(SMALL(IF(($A$1:$A$7=10)+($B$1:$B$7=10) 0,ROW($A$1:$A$7)),ROW(A1))),"",INDEX($C$1:$C$7,SM ALL(IF(($A$1:$A$6=10)+($B$1:$B$7=10)0,ROW($A$1:$A $7)),ROW(A1)),1)) Entered with Ctrl+Shift+Enter rather than just enter since this is an array formula Select H1:I1 and drag fill down the column. -- Regards, Tom Ogilvy "bijan" wrote in message ... Hi experts Please solve this plane sample with formula A B C .............................. H I 1 . . . ..............................10 1 2 10 . 1 ............................. 10 2 3 . . . ..............................10 3 4 . 10 2 .............................N/A N/A 5 . . . 6 . 10 3 7 . . . insted of N/A in last vlookup replace blank Thanks in advance |
| All times are GMT +1. The time now is 03:06 AM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com