VLOOKUP Question

[MSchiltz]

I am trying to find a formula that recognizes the amount in column F (the amounts under the 00000) and finds it in the same row (different cell). Once it finds it in that row, I would like it to return the number in the same column row 2 (the 800 numbers).

Help?!?!

Possible?!?!

Thanks!

Spend Down Expenses	Network
Account	Vendor	Invoice #	P-Card Purchase	Inv. Date	00000	890	891	895
Mileage	a				15.98	15.98
555700	b				38.76		38.76
	c				92.35			92.35
	d				262.65			262.65
	e				39.78	39.78
	f				75.98		75.98
	g				36.62		36.62
	h				766.02			766.02
	i				61.1			61.1
	j				62.04
	k				156.06
	l				335.09
	m				58.14
	n				124.44
	o				125.97
	p				326.91
	q				141.27
	r				188.7
	s				409.53
	t				466.94
					0

<tbody>
</tbody>

 

[s hal]

This isn't a place where vlookup will do what you want... You want to use a combination of Match() and Index(), as I'm assuming you want to return which of the "800" numbers match the decimal number:

Using your words that the column with 00000 is col F (I'm assuming the header is row 2), the formula would look something like the following (with only the visible portion of what you have):
=index($G$2:$I$2,1,match($F3,$G3:$I3,0))

Hope this helps!

 

[Piktro]

Rather than use a VLOOKUP, I just used a nested IF statement.

In Cell J3

=IF(F3=G3,G$2,IF(F3=H3,H$2,IF(F3=I3,I$2,"No Match")))

Drag down as necessary. Will return "No Match" if there is not a number in any column.

 

[Piktro]

This isn't a place where vlookup will do what you want... You want to use a combination of Match() and Index(), as I'm assuming you want to return which of the "800" numbers match the decimal number:

Using your words that the column with 00000 is col F (I'm assuming the header is row 2), the formula would look something like the following (with only the visible portion of what you have):
=index($G$2:$I$2,1,match($F3,$G3:$I3,0))

Hope this helps!

This works great, and is probably the better solution. I'm not great with the INDEX and MATCH functions, but it appears to return the same results.
If you are like me, and hate errors in your spreadsheets, you could further it by using

=IFERROR(INDEX($G$2:$I$2,1,MATCH($F23,$G23:$I23,0)), "xxx")

and replacing xxx with whatever you'd like.

 

[s hal]

While a nested IF could solve this, I'm assuming there is more to this spreadsheet than what was copied in, and that would get really messy, really quickly using that many IFs!

 

[MSchiltz]

Wow! Learned some cool new tricks! The amount in column F is found in that same row and then pulls the 800 number. What happens if the amount in row F is a summation of two numbers in the same row (with different departments). It will return NA. Is there any way to adjust the formula to create to lines if column F is a summation of two numbers?

Thanks!