Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
VLOOKUP OR MATCH FORMULA
I have following data in my sheet. (please see below)
Rows A B C F G---col 1 CODE AMT CODE_2 CODE K12----headings 2 SS 50 K12 SS 3 DD 20 S12 DD 4 FF 30 K12 FF 5 GG 10 E12 GG 6 HH 44 T12 HH 7 JJ 60 T12 JJ 8 KK 66 V12 KK 9 UU 70 S12 UU 10 PP 80 Y12 PP 11 ZZ 90 P12 ZZ I want formula in in cell "G2" which should match or lookup value of cell "G1" in range "C2:C11" and then match or lookup value of cell "F2" in range "A2:A11" and then bring up value from range "B2:B11" . Like in above table formula should bring value "50" in cell "G2" from column "B". I want formula something like this "lookup(G1 in C2:C11 then F2 in A2:A11 then bring value from column B). I hope I explained what I am trying to say. Please if anybody can help. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
VLOOKUP OR MATCH FORMULA
Hi,
I'm sure someone can help but i'm not clear what you are asking. Are you looking to lookup a value in a range and then return a value in another column on the same row where it was found? Eg lookup K12 in a2:f11. If the value of K12 is found in a7 return the value in b7 If not please try and explain again. |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
VLOOKUP OR MATCH FORMULA
On 11 Apr, 22:48, anon wrote:
Hi, I'm sure someone can help but i'm not clear what you are asking. Are you looking to lookup a value in a range and then return a value in another column on the same row where it was found? Eg lookup K12 in a2:f11. If the value of K12 is found in a7 return the value in b7 If not please try and explain again. look K12 in range(C2:C11) and SS which is in cell F2 in range(A2:A11) then bring value from range(B2:B11) the value should come 50 as if you see above table in row 2 we have SS in cell A2 and K12 in cell C2 so formula should pick cell B2 value which is 50. Basically with vlookup you lookup on value and then bring the answer but i want some formula which should lookup 2 values same time and then bring the result. i hope you understand what i am tring to say. |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
VLOOKUP OR MATCH FORMULA
OK got you.
=IF(AND(VLOOKUP(G1,C2:D11,1,FALSE)=G1,VLOOKUP(F2,A 2:B11,1,FALSE)=F2),VLOOKUP(F2,A2:B11,2,FALSE), 0) |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
VLOOKUP OR MATCH FORMULA
On 11 Apr, 23:18, anon wrote:
OK got you. =IF(AND(VLOOKUP(G1,C2:D11,1,FALSE)=G1,VLOOKUP(F2,A 2:B11,1,FALSE)=F2),VLOOKU*P(F2,A2:B11,2,FALSE), 0) Thanks for replying anon. i tried your formula and its work fine but when i drag this down it not working the way i want. the formula should only bring value from the row where both other value matches. it works fine in G2 cell but as i go down draging the formula its not working. any help you can give on this. thanks |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
VLOOKUP OR MATCH FORMULA
That's because the formula will be changing the ranges you're
searching in, look carefully at what has happened when you drag it down, you will see G1 has changed to G2 or G3 etc, as will all of the cells you have dragged it to. You need to put a $ in front of the cells you don't want to change. =IF(AND(VLOOKUP($G$1,$C$2:$D$11,1,FALSE)=$G$1,VLOO KUP($F$2,$A$2:$B $11,1,FALSE)=$F$2),VLOOKU**P($F$2,$A$2:$B$11,2,FAL SE),0) Suggest getting a good excel book to learn the basics of this otherwise it'll be slow going for you to do your spreadsheet. |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
VLOOKUP OR MATCH FORMULA
On 12 Apr, 09:02, anon wrote:
That's because the formula will be changing the ranges you're searching in, look carefully at what has happened when you drag it down, you will see G1 has changed to G2 or G3 etc, as will all of the cells you have dragged it to. You need to put a $ in front of the cells you don't want to change. *=IF(AND(VLOOKUP($G$1,$C$2:$D$11,1,FALSE)=$G$1,VLO OKUP($F$2,$A$2:$B $11,1,FALSE)=$F$2),VLOOKU**P($F$2,$A$2:$B$11,2,FAL SE),0) Suggest getting a good excel book to learn the basics of this otherwise it'll be slow going for you to do your spreadsheet. thanks for the advise about learning the basics but for your kind information i know more than basics may be not good as you but i am ok. i did tried putting dollar sign before but its not working. i think if you try doing on a spreadsheet than may be you know what i am trying to say. the formula you told me above is still not working |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
VLOOKUP OR MATCH FORMULA
I've tested and it seems to work for me, but without your exact
spreadsheet i can't say for sure what's happening at your end. Broken down this is what the formula is doing (so you might spot what isn't working) =IF(AND(VLOOKUP($G$1,$C$2:$D$11,1,FALSE)=$G$1, find the value in g1 somewhere in the range c2:d11 VLOOKUP($F$2,$A$2:$B$11,1,FALSE)=$F$2) now find the value in f2 somewhere in the range c2:d11 VLOOKU**P($F$2,$A$2:$B$11,2,FALSE),0) if both found then find the value of f2 somewhere in the range a2:b11 and return the value 1 column to the right of where it is found I think the problem may lie in the fact that you don't want to search for what is in F2 every time. If so post back and we can adjust. |
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
VLOOKUP OR MATCH FORMULA
On 13 Apr, 00:23, anon wrote:
I've tested and it seems to work for me, but without your exact spreadsheet i can't say for sure what's happening at your end. Broken down this is what the formula is doing (so you might spot what isn't working) =IF(AND(VLOOKUP($G$1,$C$2:$D$11,1,FALSE)=$G$1, *find the value in g1 somewhere in the range c2:d11 VLOOKUP($F$2,$A$2:$B$11,1,FALSE)=$F$2) now find the value in f2 somewhere in the range c2:d11 VLOOKU**P($F$2,$A$2:$B$11,2,FALSE),0) if both found then find the value of f2 somewhere in the range a2:b11 and return the value 1 column to the right of where it is found I think the problem may lie in the fact that you don't want to search for what is in F2 every time. If so post back and we can adjust. Please see the link below where i uploaded my file and explained everything which will be easy for you to understand http://www.savefile.com/files/1501428 |
#10
Posted to microsoft.public.excel.programming
|
|||
|
|||
VLOOKUP OR MATCH FORMULA
OK I've had a look.
I think you want on every row to look for G1 in column C and look for F(row number) in column B and if both found on the same row return the value in column B. If this is what you need the formula below works. It only returns a value in your spreadsheet in cell G2 as no other rows have both K12 and the valuein column F found in columns A & C. =IF(C2=$G$1,IF(A2=F2,B2,0),0) Hopefully this is what you need now. |
#11
Posted to microsoft.public.excel.programming
|
|||
|
|||
VLOOKUP OR MATCH FORMULA
On 13 Apr, 09:35, anon wrote:
OK I've had a look. I think you want on every row to look for G1 in column C and look for F(row number) in column B and if both found on the same row return the value in column B. If this is what you need the formula below works. It only returns a value in your spreadsheet in cell G2 as no other rows have both K12 and the valuein column F found in columns A & C. =IF(C2=$G$1,IF(A2=F2,B2,0),0) Hopefully this is what you need now. your fromula works fine if i have both table in one sheet but think if i have two sheets in sheet 1 i have table which was in col A to C in other words in which value need to be lookup and sheet 2 where we need to put formula then how you'll make your formula. for example if you check my spreadsheet think that the table which was in col A to C is in sheet 1 and table in col F to G is in sheet 2 where we need to put formula then how you'll write your fomula. sorry to be pain |
#12
Posted to microsoft.public.excel.programming
|
|||
|
|||
VLOOKUP OR MATCH FORMULA
=IF(C2=$G$1,IF(A2=F2,B2,0),0) any cell referred to in this formula is on the sheet where the formula is placed. if you want to refer to cells on other sheets you need to tell the formula which sheet you are referring to; example; =IF(Sheet1!C2=sheet1!$G$1, IF(Sheet2!A2=Sheet2!F2,Sheet1!B2,0),0) You will need to work out which cells are on which sheet and change the sheet names accordingly. Remember to use ! after the sheet name. |
#13
Posted to microsoft.public.excel.programming
|
|||
|
|||
VLOOKUP OR MATCH FORMULA
On 13 Apr, 10:28, anon wrote:
*=IF(C2=$G$1,IF(A2=F2,B2,0),0) any cell referred to in this formula is on the sheet where the formula is placed. if you want to refer to cells on other sheets you need to tell the formula which sheet you are referring to; example; =IF(Sheet1!C2=sheet1!$G$1, IF(Sheet2!A2=Sheet2!F2,Sheet1!B2,0),0) You will need to work out which cells are on which sheet and change the sheet names accordingly. Remember to use ! after the sheet name. Thanks anon and sorry to bother you too much |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Formula Using Vlookup & Match | Excel Worksheet Functions | |||
vlookup or index/match formula?? | Excel Worksheet Functions | |||
Help with VLOOKUP & MATCH formula | Excel Worksheet Functions | |||
n/a in vlookup/index/match formula | Excel Worksheet Functions | |||
Match / Vlookup within an Array formula | Excel Discussion (Misc queries) |