USER EMAIL : VLookup on multiple sheets at a time

Question:

 Dear Sir,

kindly advise how i can combine VLOOKUP with LOOKUP in one formula. because i like to search for a specific number in two execl sheet & if the number not found in the first sheet it should give me the result from the second sheet.

Appreciate your quick response.

Best Regards
R*******n A***u



Answer: 

Hi there,

Suppose sheet1 has :

  A      |     B     

-----------------------
1   |  1      |    A
2   |  2      |    B
3   |  3      |    C
4   |  4      |    D
5   |  5      |    E

And Sheet2 has

  A      |     B     
-----------------------
1   |  6      |    AA
2   |  7      |    BB
3   |  8      |    CC
4   |  9      |    DD
5   |  10    |    EE

Let's assume that your search value is in D1

=IFERROR(VLOOKUP(D1,Sheet1!A1:B5,2,0),VLOOKUP(D1,Sheet2!A1:B5,2,0))

X =  formula in Green

Y = formula in Cyan

If X is valid then it will give result. If there is error in X then return Y.

Hope this makes sense.

Regards,

Kamal

USER EMAIL : Conditional formatting using formula

Problem :

I have a small query and need your help...

How to do a conditional formatting of  a cell in excel that it accepts only any alphanumeric of 8 digits where the first 4 digits are alpha and next 4 digits are numeric in this fashion for e.g (QBCD1234) and in case the user ​feeds "0" inplace of Q it does not accept and a error message is given out...

Solution :

1. Open VBA editor and paste this code in a new module.

Function CFunc(str As String)

	If Len(str) > 4 Then
	     flag = True

            For i = 1 To 4

				If Not (Asc(Mid(UCase(str), i, 1)) > 64 And Asc(Mid(UCase(str), i, 1)) < 92)

					flag = False

				End If

			Next
	
		CFunc = flag

    Else

        CFunc = False

    End If

End Function

   

2. Select cell A1 and create a rule in Conditional Formatting. Then select  "Use a formula to determine which cells to format"

Enter formula : =cFunc(A1)

Click Format and set fill color to red

Copy this formula to other cells wherever applicable.

Output :

The cells where first four characters are alphabets then it will change the cell color to red.