VLookup Formula

[satish gubbi]

Hi All,

I request you help in the below formula,

IF(Vlookup(A1,D1,1,0),A1,if(Vlookup(A2,D1,1,0),A2,if(Vlookup(A3,D1,1,0),A3. ....... so on for 50+ cells to looked / validated in D1

I am getting error #N/A, although I used If(ISNA, IF(ISERROR, all possible syntax.

Please help.

[p45cal]

does this give the similar results?:
=IF(COUNTIF(A1:A50,D1)>0,D1,"not there")

if you must get the result from column A then maybe?:
=IFERROR(INDEX(A1:A50,MATCH(D1,A1:A50,0)),"not there")

[satish gubbi]

Hi P45cal,

Thank you very much for your help, first formula did help me, I did some changes with the formula and I am getting the error, can you please help me in rectifying the formula.

=IF(COUNTIF(H2,UserList!A1)>0,UserList!A1,IF(COUNTIF(H2,UserList!A2)>0,UserList!A2,IF(COUNTIF(H2,UserList!A3)>0,UserList!A3,IF(COUNTIF(H2,UserList!A4)>0,UserList!A4,IF(COUNTIF(H2,UserList!A5)>0,UserList!A5,IF(COUNTIF(H2,UserList!A6)>0,UserList!A6,IF(COUNTIF(H2,UserList!A7)>0,UserList!A7,IF(COUNTIF(H2,UserList!A8)>0,UserList!A8,IF(COUNTIF(H2,UserList!A9)>0,UserList!A9,IF(COUNTIF(H2,UserList!A10)>0,UserList!A10,IF(COUNTIF(H2,UserList!A11)>0,UserList!A11,IF(COUNTIF(H2,UserList!A12)>0,UserList!A12,IF(COUNTIF(H2,UserList!A13)>0,UserList!A13,IF(COUNTIF(H2,UserList!A14)>0,UserList!A14,IF(COUNTIF(H2,UserList!A15)>0,UserList!A15,IF(COUNTIF(H2,UserList!A16)>0,UserList!A16,IF(COUNTIF(H2,UserList!A17)>0,UserList!A17,IF(COUNTIF(H2,UserList!A18)>0,UserList!A18,IF(COUNTIF(H2,UserList!A19)>0,UserList!A19,IF(COUNTIF(H2,UserList!A20)>0,UserList!A20,IF(COUNTIF(H2,UserList!A21)>0,UserList!A21,IF(COUNTIF(H2,UserList!A22)>0,UserList!A22,IF(COUNTIF(H2,UserList!A23)>0,UserList!A23,IF(COUNTIF(H2,UserList!A24)>0,UserList!A24,IF(COUNTIF(H2,UserList!A25)>0,UserList!A25,IF(COUNTIF(H2,UserList!A26)>0,UserList!A26,IF(COUNTIF(H2,UserList!A27)>0,UserList!A27,IF(COUNTIF(H2,UserList!A28)>0,UserList!A28,IF(COUNTIF(H2,UserList!A29)>0,UserList!A29,IF(COUNTIF(H2,UserList!A30)>0,UserList!A30,IF(COUNTIF(H2,UserList!A31)>0,UserList!A31,IF(COUNTIF(H2,UserList!A32)>0,UserList!A32,IF(COUNTIF(H2,UserList!A33)>0,UserList!A33,IF(COUNTIF(H2,UserList!A34)>0,UserList!A34,IF(COUNTIF(H2,UserList!A35)>0,UserList!A35,IF(COUNTIF(H2,UserList!A36)>0,UserList!A36,IF(COUNTIF(H2,UserList!A37)>0,UserList!A37,IF(COUNTIF(H2,UserList!A38)>0,UserList!A38,IF(COUNTIF(H2,UserList!A39)>0,UserList!A39,IF(COUNTIF(H2,UserList!A40)>0,UserList!A40,IF(COUNTIF(H2,UserList!A41)>0,UserList!A41,IF(COUNTIF(H2,UserList!A42)>0,UserList!A42,IF(COUNTIF(H2,UserList!A43)>0,UserList!A43,IF(COUNTIF(H2,UserList!A44)>0,UserList!A44,IF(COUNTIF(H2,UserList!A45)>0,UserList!A45,IF(COUNTIF(H2,UserList!A46)>0,UserList!A46,IF(COUNTIF(H2,UserList!A47)>0,UserList!A47,IF(COUNTIF(H2,UserList!A48)>0,UserList!A48,IF(COUNTIF(H2,UserList!A49)>0,UserList!A49,IF(COUNTIF(H2,UserList!A50)>0,UserList!A50..........................) so on till A150.

[p45cal]

I don't think you need such a long formula.
Try:
=IF(COUNTIF(UserList!$A$1:$A$150,H2)>0,H2,"not there")

[satish gubbi]

Thank you very much for quick reply, Please find attached file that I am working on

There are two tabs in the spreadsheet one is User ID and Updated

there are batch descriptions that contains UserID and which needs to be looked from UserID list. If the UserID is found in the "Updated" spreadsheet then User ID to be placed in the next column, if one User ID is not found then look for other User ID.

[p45cal]

It is good that you decided to upload a file since what you're wanting could not even have been guessed at, not even from your formulae.
In B2 of the Updated sheet ARRAY-ENTER (enter the formula with Ctrl+Shift+Enter, not just Enter, otherwise you will get an error such as #REF!):
=INDEX(UserList!$A$1:$A$108,MIN(IF(ISERR(SEARCH(UserList!$A$2:$A$108,A2)),1 E+99,ROW(UserList!$A$2:$A$108))))
then copy down.
This is in the attached file.

You could show a not-found name with "-" by altering the formula to:
=IFERROR(INDEX(UserList!$A$1:$A$108,MIN(IF(ISERR(SEARCH(UserList!$A$2:$A$10 8,A2)),1E+99,ROW(UserList!$A$2:$A$108)))),"-")
but be sure to ARRAY-ENTER this formula otherwise all results will be "-".

[satish gubbi]

Hi P45cal,

I dont have words to express my gratitude for this... you are like a "Diety" for me. thank you very much, this is what I was looking for.

Thank you very much again.

[ExcelAndAccess]

For those interested in the VLookup function, here is the link to the video Zack Barresse did last year. It is the first entry on the Twitter page. https://twitter.com/vbaexpress

You can see a few of Zack's other recent recordings on the VBAX Video page. http://www.vbaexpress.com/vbae-videos.html