Vlookup with two tables?

**hydraonstimpac** · 02-12-2012, 11:33 AM

Hello so i have a formula question, i need to only use Vlookup, IF, and other very basic functions to do it. So i need a formula in B11 that will tell me the price of the Item, times the number of those items listed, AND if the item you type in an item that is not there (like say you try to look up Hat, but obviously there arent any hats in the table...) then the price reads OUT OF STOCK. Take a look and see if you can help me please!!

**hydraonstimpac** · 02-12-2012, 11:44 AM

and i can not move that data around it has to stay where it is

**canapone** · 02-12-2012, 12:03 PM

Hi,

maybe

Please Login or Register  to view this content.

There are for sure better solutions.

Regards

**hydraonstimpac** · 02-12-2012, 12:10 PM

this returned the correct price values!!! although it did not multiply by the number of items sold, and i cant get that working??

**mahju** · 02-12-2012, 12:18 PM

Hi

May be this one

Please Login or Register  to view this content.

Regards

**canapone** · 02-12-2012, 12:20 PM

Hi,

if the number is in B10:

Please Login or Register  to view this content.

Regards

**hydraonstimpac** · 02-12-2012, 03:13 PM

thank you!! they both work!

**Haseeb Avarakkan** · 02-12-2012, 07:54 PM

Since you are on XL 2010,

=IFERROR(VLOOKUP(B9,A4:B6,2,0)*B10,IFERROR(VLOOKUP(B9,D4:E6,2,0)*B10,"Out of Stock"))

Or with simple SUMIF,

=SUMIF(A4:E6,B9,B4:E6)*B10

Format the cell custom as,

General;-General;"Out of Stock"