Tech Support Guy banner
Cancel

Excel Database Help

1808 Views 20 Replies 5 Participants Last post by  Zack Barresse, 
R
I am working on a database where A1-A1500 contains part numbers. Columns B-BX contain "Vendor Price" and "Date Price was Quoted" for multiple vendors. I inserted 2 columns at Column B and added "Best Price" into Column B and did MIN function to find the lowest price. Ok, that was the easy part.

Now I would like to/have to work out some logic so the database knows which Vendor did have the lowest price because I need to have the "Date Price was Quoted" that is associated with the "Best Price" fill into Column C. I think this is a combination of a VLookup and an if then statement, but I am not sure how to approach it and I am stuck! Help!

I appreciate any insight/advice..
Thanks for your time,

Rudy
Save
Like
Status
Not open for further replies.
1 - 2 of 21 Posts
Zack Barresse
No VBA needed, just enter this formula in B2, copy down...

Code: 
=IF(ISNUMBER(MATCH($B2,D2:OFFSET(INDEX($1:$1,1,MATCH(REPT("z",255),$1:$1)),ROW(A2)-1,0),0)),INDIRECT(ADDRESS(ROW(A2),MATCH($B2,D2:OFFSET(INDEX($1:$1,1,MATCH(REPT("z",255),$1:$1)),ROW(A2)-1,0),0)+4,4)),0)
HTH
Save
Like
Zack Barresse
Thanks! I probably could have shortened it with a defined name, but I shy away from them unless really needed. I'm sure there are better ways, but it works. :)
Save
Like
1 - 2 of 21 Posts
Status
Not open for further replies.
Tech Support Guy
A forum community dedicated to tech experts and enthusiasts. Come join the discussion about articles, computer security, Mac, Microsoft, Linux, hardware, networking, gaming, reviews, accessories, and more!
Full Forum Listing
Explore Our Forums
Hardware Virus & Other Malware Removal Off Topic Lounge Thread Games & Discussion Networking
Top