excel formula question pretty please 12:29 - Sep 30 with 1050 views | giant_stow | How do I copy the cell to the right using relative refs in a formula? So I want to check down col A and where I find a match in G, say in row 7, I want to copy H7 to j7? Got as far as this: I'm guessing the bit I need to add is to replace "yes" =IF(ISERROR(VLOOKUP(A2,$G$2:$G$5000,1,0)),"NO","YES") Sorry if this is a piss take, but I've been hitting google all morning and am stumped! Edit: it has to be relative as I need to search down a long list of rows. [Post edited 30 Sep 2020 12:32]
| |
| | |
excel formula question pretty please on 12:33 - Sep 30 with 1018 views | factual_blue | I think you remove one of the dollar signs. I think it's the one before the letter rather than the one before the number. Hoever thhis is a fast-fading memory so I might be totally wrong. And you need to replace the "yes" with "balls". And surely you norfukkers count better using beans? | |
| |
excel formula question pretty please on 12:37 - Sep 30 with 978 views | giant_stow |
excel formula question pretty please on 12:33 - Sep 30 by factual_blue | I think you remove one of the dollar signs. I think it's the one before the letter rather than the one before the number. Hoever thhis is a fast-fading memory so I might be totally wrong. And you need to replace the "yes" with "balls". And surely you norfukkers count better using beans? |
I'd rather be counting beans for sure. Thanks for the tip - off to try it. | |
| |
excel formula question pretty please on 12:46 - Sep 30 with 953 views | clive_baker | I'm not entirely sure I understand what you're asking. Your lookup value is in column A, and your range is column G. If the value in column A occurs in column G, you want to copy the value in column H to column J? This formula in column J might do the trick, unless I've misunderstood: =IFERROR(IF(VLOOKUP(A1,$G$1:$G$200,1,0),H1,"-"),0) [Post edited 30 Sep 2020 12:48]
| |
| |
excel formula question pretty please on 12:47 - Sep 30 with 947 views | Clapham_Junction | Is this row specific (so only rows where A and G match), or any cases where what's in column G exists somewhere in column A? | | | |
excel formula question pretty please on 12:50 - Sep 30 with 927 views | giant_stow |
excel formula question pretty please on 12:46 - Sep 30 by clive_baker | I'm not entirely sure I understand what you're asking. Your lookup value is in column A, and your range is column G. If the value in column A occurs in column G, you want to copy the value in column H to column J? This formula in column J might do the trick, unless I've misunderstood: =IFERROR(IF(VLOOKUP(A1,$G$1:$G$200,1,0),H1,"-"),0) [Post edited 30 Sep 2020 12:48]
|
Yeah thats part of my problem - not entirely sure what I'm asking! Off to try it - thanks! Edit: no joy, but thanks for trying. appreciated. [Post edited 30 Sep 2020 12:55]
| |
| |
excel formula question pretty please on 12:53 - Sep 30 with 915 views | giant_stow |
excel formula question pretty please on 12:47 - Sep 30 by Clapham_Junction | Is this row specific (so only rows where A and G match), or any cases where what's in column G exists somewhere in column A? |
Thanks for the answer - what's in Col A and G won't match by row, so the latter. basically, i have a list of countries in Col A (where an order was placed), a list of countries in col G and need to copy a Sage country code for each transaction (country codes in Col H) I want to end up with: A / J Transaction country / Country code | |
| |
excel formula question pretty please on 13:01 - Sep 30 with 889 views | Clapham_Junction |
excel formula question pretty please on 12:53 - Sep 30 by giant_stow | Thanks for the answer - what's in Col A and G won't match by row, so the latter. basically, i have a list of countries in Col A (where an order was placed), a list of countries in col G and need to copy a Sage country code for each transaction (country codes in Col H) I want to end up with: A / J Transaction country / Country code |
Try (in cell J2) =IFERROR(IF(MATCH(G2,$A:$A,0),H2),"") [Post edited 30 Sep 2020 13:03]
| | | |
excel formula question pretty please on 13:08 - Sep 30 with 862 views | giant_stow |
excel formula question pretty please on 13:01 - Sep 30 by Clapham_Junction | Try (in cell J2) =IFERROR(IF(MATCH(G2,$A:$A,0),H2),"") [Post edited 30 Sep 2020 13:03]
|
Thanks loads - not quite there, but I think I see where you're going, so will try from there. Much appreciated - you're a gent. | |
| | Login to get fewer ads
excel formula question pretty please on 13:42 - Sep 30 with 809 views | StokieBlue | =IF(ISNUMBER(MATCH(A2,$G$2:$G$50000,0)),INDEX($H$2:$H$50000,MATCH(A2,$G$2:$G$50000,0)),"NO") SB | |
| Avatar - IC410 - Tadpoles Nebula |
| |
excel formula question pretty please on 13:49 - Sep 30 with 791 views | giant_stow |
excel formula question pretty please on 13:42 - Sep 30 by StokieBlue | =IF(ISNUMBER(MATCH(A2,$G$2:$G$50000,0)),INDEX($H$2:$H$50000,MATCH(A2,$G$2:$G$50000,0)),"NO") SB |
You did it! I don't know how (edit, as in I don;t really understand the code but it works), but thanks so much. pm to come [Post edited 30 Sep 2020 13:52]
| |
| |
excel formula question pretty please on 14:24 - Sep 30 with 743 views | Clapham_Junction |
excel formula question pretty please on 13:42 - Sep 30 by StokieBlue | =IF(ISNUMBER(MATCH(A2,$G$2:$G$50000,0)),INDEX($H$2:$H$50000,MATCH(A2,$G$2:$G$50000,0)),"NO") SB |
Ah, it looks like I misunderstood ullaa's request. I thought column G was the primary column for selecting the match, not column A... If you reverse the A and G in the formula I suggested above, I think it should work too? | | | |
excel formula question pretty please on 14:37 - Sep 30 with 721 views | StokieBlue |
excel formula question pretty please on 14:24 - Sep 30 by Clapham_Junction | Ah, it looks like I misunderstood ullaa's request. I thought column G was the primary column for selecting the match, not column A... If you reverse the A and G in the formula I suggested above, I think it should work too? |
I don't think so because the values in G and H are not synchronised with the values in A so in the formula you posted where you say H2 needs to be a column range and the correct index needs to be selected with the result of the lookup of A against G. I've not thought about it a lot though so you might be right! SB [Post edited 30 Sep 2020 15:02]
| |
| Avatar - IC410 - Tadpoles Nebula |
| |
excel formula question pretty please on 14:56 - Sep 30 with 697 views | Keno | surely with all your extra digits you can just do this by hand and maybe foot? (sorry I cant offer any actual help but Id be interested to know if you get it sorted) | |
| |
excel formula question pretty please on 18:22 - Sep 30 with 597 views | giant_stow |
excel formula question pretty please on 14:56 - Sep 30 by Keno | surely with all your extra digits you can just do this by hand and maybe foot? (sorry I cant offer any actual help but Id be interested to know if you get it sorted) |
The numerous extra digits (I'm actually a record breaker in my world) were no use in this case, but thanks to everyone again. | |
| |
excel formula question pretty please on 18:28 - Sep 30 with 586 views | Keno |
excel formula question pretty please on 18:22 - Sep 30 by giant_stow | The numerous extra digits (I'm actually a record breaker in my world) were no use in this case, but thanks to everyone again. |
I was chuffed when I learnt how to do compound interest on an excel sheet its a very handy bit of software | |
| |
| |