Multiple Vlookups in one

[Austin R]

Hello.

I have a sheet that has a master list of all the item employees in my company have signed out, I am making a sheet to pull that information in based off a search of an employee. Not all employees have the same equipment signed out so some of the cells are blank.

Here is what the sheet of data looks like.

And here is what the search sheet looks like.

In H7 I currently have =IF(ISBLANK(VLOOKUP(A7,Sheet2!A:G,3,FALSE)),"BLANK",VLOOKUP(A7,Sheet2!A:G,3,FALSE))

What I am trying to do is if the cell that it is looking up is blank or not, if it is not blank I want the "Serial Number" to transfer over, and if it is blank I want to search the next cell and basically repeat the process. The problem I am running into is whenever I replace the value_if_true portion ("Blank") with another IF(ISBLANK(VLOOKUP) formula everything stops working.

Thank you all in advance for your help!

This is not the exact information I am working with. The real sheet is on my work computer, I am just trying to find the solution before I go back in.

 

[rondeondo]

Hi Austin,
Vlookup will return a value or an NA. If the search data is found it will return the value in the cell you've reference, if the cell is blank it will return a 0 and isblank() will be false. So in your IF look for 0 or perhaps "" as an empty string, depends what's in your source data.
Can be handy to use the formula tab --> "evaluate formula" function for these types of issues. It steps through your formula and shows the values along each step of execution.

 

[kweaver]

Since you have Excel 365, you should look into XLOOLUP.

I would be easier on anyone helping you if you used XL2BB to show your sample sheet.

 

[kweaver]

Also, by the way, how do you want the results shown? All in 1 horizontal line across from the 22 or vertical?

 

[Austin R]

Also, by the way, how do you want the results shown? All in 1 horizontal line across from the 22 or vertical?

The results will be shown one item in each row. So row 7 would show the first result, row 8 would show the second result and so on.

 

[Austin R]

Hi Austin,
Vlookup will return a value or an NA. If the search data is found it will return the value in the cell you've reference, if the cell is blank it will return a 0 and isblank() will be false. So in your IF look for 0 or perhaps "" as an empty string, depends what's in your source data.
Can be handy to use the formula tab --> "evaluate formula" function for these types of issues. It steps through your formula and shows the values along each step of execution.

Thank you for your reply, I'll give this a shot.

 

[Austin R]

Book1
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U	V	W	X	Y	Z	AA	AB
1
2		HAND RECEIPT / ANNEX NUMBER For use of this form, see DA PAM 710-2-1. The proponent agency is ODCSLOG.								FROM:								TO:								HAND RECEIPT NUMBER
3
4		FOR ANNEX / CR ONLY		END ITEM STOCK NUMBER					END ITEM DESCRIPTION							PUBLICATION NUMBER						PUBLICATION DATE				QUANTITY
5
6	SEARCH ADMIN NUMBER	STOCK NUMBER a.						ITEM DESCRIPTION b.											* C.	SEC d.	UI e.	QTY AUTH f.	g. QUANTITY
7	22																						A	B	C	D	E	F
8								BLANK
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32		* WHEN USE AS A:
33				HAND RECEIPT, enter Hand Receipt Annex Number
34				HAND RECEIPT FOR QUARTERS FURNITURE, enter Condition Codes
35				HAND RECEIPT ANNEX/COMPONENTS RECEIPT, enter Accounting Requirements Code (ARC) PAGE _________ OF ________ PAGES
36
Sheet1

Cell Formulas
Range	Formula
H8	H8	=IF(ISBLANK(VLOOKUP(A7,Sheet2!A:G,3,FALSE)),"BLANK",VLOOKUP(A7,Sheet2!A:G,3,FALSE))

Book1
	A	B	C	D	E	F	G	H
1	ADMIN NUMBER	NAME	COMPUTRER	PHONE	PRINTER	VEHICLE	TABLET
2	21	Davis	Dell 2	Samsung33			Samsung1
3	22	Jack		iPhone12	HP47	Tahoe
4
5
6
7
8
Sheet2

 

[kweaver]

Try this in H7:

=TRANSPOSE(FILTER(XLOOKUP(A7,Employees!A2:A11,Employees!C2:G11),0<>XLOOKUP(A7,Employees!A2:A11,Employees!C2:G11)))

 

[Austin R]

Try this in H7:

=TRANSPOSE(FILTER(XLOOKUP(A7,Employees!A2:A11,Employees!C2:G11),0<>XLOOKUP(A7,Employees!A2:A11,Employees!C2:G11)))

Thank you so much!! It worked!

 

[kweaver]

Great. Happy to help. Please mark it solved.