Excel: IF - Then Function, (Potentially Incorporating V/Hlookup

[Soulknight12]

Hi All,

I'm seeking assistance in building a cell formula that would search a table for any values within that row. If there is a value within that row, it will give me the Title of that row for example:

Ideally what I need is some sort of function that would tell me which bond each customer has, without having to check manually.

Thanks alot in advance for those that can help!
If you can post what formula to use it would be appreciated.

 

[jtakw]

Hi,

Assuming no customer holds 2 or more Bonds (as in your sample), try something like this:

Book3.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L
3		Bonds held
4			BOND 1	BOND 2	BOND 3	BOND 4	BOND 5	BOND 6	BOND 7	BOND 8	BOND 9	BOND 10
5	Jaime	BOND 5					20000
6	Joshua	BOND 2		1000
7	Freedom	BOND 6						30000
8	Simon	BOND 7							4000
9	Patrick	BOND 4				1231
Sheet769

Cell Formulas
Range	Formula
B5:B9	B5	=LOOKUP(9.99999999999999E+307,C5:L5,C$4:L$4)

 

[AhoyNC]

With Excel 365 you could use the FILTER function.
If the customer had more than one bond then something like the formula in cell B8 below,

Book1
	A	B	C	D	E	F	G	H	I	J	K	L
1		Bonds held
2			BOND 1	BOND 2	BOND 3	BOND 4	BOND 5	BOND 6	BOND 7	BOND 8	BOND 9	BOND 10
3	Jaime	BOND 5					20000
4	Joshua	BOND 2		1000
5	Freedom	BOND 6						30000
6	Simon	BOND 7							4000
7	Patrick	BOND 4				1231
8	Bill	BOND 2, BOND 9		4000							600
Sheet2

Cell Formulas
Range	Formula
B3:B7	B3	=FILTER($C$2:$L$2,$C3:$L3<>"","No Bond")
B8	B8	=TEXTJOIN(", ",1,FILTER($C$2:$L$2,$C8:$L8<>"","No Bond"))

 

[Soulknight12]

Hi,

Assuming no customer holds 2 or more Bonds (as in your sample), try something like this:

Book3.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L
3		Bonds held
4			BOND 1	BOND 2	BOND 3	BOND 4	BOND 5	BOND 6	BOND 7	BOND 8	BOND 9	BOND 10
5	Jaime	BOND 5					20000
6	Joshua	BOND 2		1000
7	Freedom	BOND 6						30000
8	Simon	BOND 7							4000
9	Patrick	BOND 4				1231
Sheet769

Cell Formulas
Range	Formula
B5:B9	B5	=LOOKUP(9.99999999999999E+307,C5:L5,C$4:L$4)

Hi There,

I'm having some trouble using this look up function, this is a similar variant to what I'm trying to do on my excel sheet

Test Sheet.xlsx
	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	AC	AD	AE	AF	AG	AH	AI	AJ	AK	AL	AM	AN	AO	AP	AQ	AR	AS	AT	AU	AV	AW	AX	AY
3	Matured	Account Holder	Bond Holdings (Stock Code)	Prices used	Bahamas 2024	Bahamas 2028	Bahamas 2029	Bermuda 24	FCB 2023	GORTT 24	GORTT 3.4 2021	GORTT 2022	GORTT 3.3 2023	GORTT 2023	GORTT 2025	GORTT 2026	Gortt 2032	Gortt 2033	Gortt 2036	HODECO 2023	HODECO 2024	HMB 2024	HMB 2025	MASSY 2024	MTS	NIDCO 2028	NIDCO 2031	NIPDEC 2025	NIPDEC 2028	NIPDEC 5.02% 2029	NIPDEC 2029	NIPDEC 2030	NCB 2021	NIF 2023	NIF 2030	NIF 2038	SURINM 26	TBILL 16 Jun 2021	TBILL 31 Dec 2020	TTMORF 2023	TTMORF 2024	TTMF 4.75% 2025	TTMORF 2026	TTMF 4.94 2026	TSTT 8.3% 2029	TRITOB 6.3 2023	TRITOB 2024	TRITOB 2026	TRITOB 2027	TRNGEN 2027	UNICOM 2024
4
5	No	Kenny Mohammed and Patricia Mohammed	#N/A	$112.03											189000
6
7
Sheet3

Cell Formulas
Range	Formula
C5	C5	=LOOKUP(9.99999999999999+307,E5:AY5,E4:AY4)

 

[jtakw]

You have a couple of mistakes in the formula in C5, it should be:

=LOOKUP(9.99999999999999E+307,E5:AY5,E3:AY3)

Book3.xlsx
	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	AC	AD	AE	AF	AG	AH	AI	AJ	AK	AL	AM	AN	AO	AP	AQ	AR	AS	AT	AU	AV	AW	AX	AY
3	Matured	Account Holder	Bond Holdings (Stock Code)	Prices used	Bahamas 2024	Bahamas 2028	Bahamas 2029	Bermuda 24	FCB 2023	GORTT 24	GORTT 3.4 2021	GORTT 2022	GORTT 3.3 2023	GORTT 2023	GORTT 2025	GORTT 2026	Gortt 2032	Gortt 2033	Gortt 2036	HODECO 2023	HODECO 2024	HMB 2024	HMB 2025	MASSY 2024	MTS	NIDCO 2028	NIDCO 2031	NIPDEC 2025	NIPDEC 2028	NIPDEC 5.02% 2029	NIPDEC 2029	NIPDEC 2030	NCB 2021	NIF 2023	NIF 2030	NIF 2038	SURINM 26	TBILL 16 Jun 2021	TBILL 31 Dec 2020	TTMORF 2023	TTMORF 2024	TTMF 4.75% 2025	TTMORF 2026	TTMF 4.94 2026	TSTT 8.3% 2029	TRITOB 6.3 2023	TRITOB 2024	TRITOB 2026	TRITOB 2027	TRNGEN 2027	UNICOM 2024
4
5	No	Kenny Mohammed and Patricia Mohammed	GORTT 2025	112.03											189000
Sheet769

Cell Formulas
Range	Formula
C5	C5	=LOOKUP(9.99999999999999E+307,E5:AY5,E3:AY3)

 

[Soulknight12]

Hi, Ended up figuring that out today, Thank you so much for the assistance mate, Really appreciate it.

 

[jtakw]

You're welcome, glad you got it working.