vlookup with match function

[Anshum Manocha]

Hello guys, I'm trying to use vlookup of product code with matching price cell value but i'm unable to go through full process. I know its a easy job for talented guys like you, please help me out.

there are 3 sheets in my excel workbook,
Sheet1 = PRICE Sheet
Sheet2 = Data from web (which I've to update every week to confirm the stock in hand)
Sheet3 = "Refined" sheet is the sheet in which I've to calculate the price of stock with the product quantity.

I need to update price from the sheet1 and quantity from sheet 2, "available" column in sheet 3. as there are duplicate values in sheet2, so I've to pick only the product available quantity with matching the price value of sheet 1


Thanks

 

[mohamed ilyas]

Hi

what version excel you are using?

 

[bosco_yip]

Try,

In F4, formula copied down:

=LOOKUP(9^9,'FROM WEB'!F:F/('FROM WEB'!B:B=B4)/('FROM WEB'!H:H=E4))

 

[Anshum Manocha]

Hi

what version excel you are using?

multiple versions as one system is with windows 7 and office 2007, rest with latest technology

 

[Anshum Manocha]

Try,

In F4, formula copied down:

=LOOKUP(9^9,'FROM WEB'!F:F/('FROM WEB'!B:B=B4)/('FROM WEB'!H:H=E4))

View attachment 85383

thankyou sir, it is working but taking alot of time to load as if i update even 1 file or 1 value, it hangs up and take 3-5 minute in i5 processor

 

[bosco_yip]

thankyou sir, it is working but taking alot of time to load as if i update even 1 file or 1 value, it hangs up and take 3-5 minute in i5 processor

Then, try this

=LOOKUP(9^9,'FROM WEB'!F1:F1000/('FROM WEB'!B1:B1000=B4)/('FROM WEB'!H1:H1000=E4))

 

[Anshum Manocha]

ok thanks a lot.