| excel formula question pretty please 12:29 - Sep 30 with 1044 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 1012 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 972 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 947 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 941 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 921 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 909 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 883 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 856 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 803 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 785 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 737 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 715 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 691 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 591 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 580 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 | |
| |
| |
By continuing to use the site, you agree to our use of cookies and to abide by our Terms and Conditions.
