vlookup i think

[samantha]

Hello,

this is my scenario On sheet 1 i have a list of values in colum B that
represent account numbers, if the account number in Column B Sheet 1 appears
in the list of hold accounts on Sheet two, i want to return a value of 'Hold"
in column A of sheet 1. I can't seem to get the syntax or formual correct.
Any help would be greatly appreciated, if i can't get the formual to work i
will have to match it all manually and with 1200 l ines of data that is going
to hurt.

Thanks!!!!

 

[Ashish Mathur]

Hi,

In column A of sheet 1 (say cell A2), you could use
=if(countif(Sheet2!$C$2:$C$500,B2)>=1,"Hold","Release")

--
Regards,

Ashish Mathur
Microsoft Excel MVP
www.ashishmathur.com

 

[Eduardo]

Hi,
I assumed that in Sheet2 account numbers are in column a so use this formula
in sheet 1 column a

=IF(SUMPRODUCT(--(B1=Sheet2!A1:A13))>0,"Hold","")

if the account is not find will leave a blank

if this helps please click yes thanks

 

[Ms-Exl-Learner]

Yes you have to use Vlookup.

Have a look in the below example.

=VLOOKUP(lookup value,Table Array,Column Index Number,Range Lookup)

=VLOOKUP(Sheet1!B1,Sheet2!A:F,3,FALSE)

Lookup Value:-
Sheet1!B1 - Account No. in Sheet B1 cell

Table Array:-
Sheet2!A:F is the table array range.
Note that the range should start from the column on which the Account Number
is lying. (i.e.) if the Account Number in Sheet2 is in E Column then the
Table Array should start from Sheet2!E. Here i assumed that the account no
is in sheet2!A, so i have used sheet2!A:F

Column Index Number:-
3 - If the word "HOLD" lying in C Column Then you have to mention it as 3
because if you calculate from A Column to C Column it is 3rd Column. So we
have to mention it as 3.

Range Lookup:-
FALSE - False is the default Word and instead of False you can also use 0
also.

Finallly use If function to complete the formula:-

=IF(ISNA(VLOOKUP(Sheet1!B1,Sheet2!A:F,3,FALSE)),"",VLOOKUP(Sheet1!B1,Sheet2!A:F,3,FALSE))

If this post helps, Click Yes!